﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using BHXH_NT.XML;
using Microsoft.VisualBasic;
using System.Transactions;
using BHXH_NT.Properties;
using BHXH_NT.Data;
using CrystalDecisions.CrystalReports.Engine;

namespace BHXH_NT
{
    public partial class frmHSCN : Form
    {
        private const string XML_FILE_NGUYENQUAN = "NguyenQuanObject.xml";
        private const string XML_FILE_CONGVIEC = "CongViecObject.xml";

        private string connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
        private string connectionString1 = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString1;
        private string connectionString2 = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString2;
        private string strsobhxh;
        private string strsokcb;
        private string strKeyls;
        private int Flaggrid;
        private Boolean FlagSendkey;
        Point ptgroup;
        danhsachthe dsDSNV = new danhsachthe();
        Random rnd = new Random();

        public frmHSCN()
        {
            InitializeComponent();
        }
        #region "Event Method"
        private void HSCN_Load(object sender, EventArgs e)
        {
            GetNguyenQuan();
            GetCongViec();
            GetDataforDanToc();
            GetDataforQuoctich();
            GetDataforTinh();
            GetDataforLyDo();
            faNV1.Rows.DefaultSize = faNV1.Rows.DefaultSize + 5;
            faNV2.Rows.DefaultSize = faNV2.Rows.DefaultSize + 5;
            faNV3.Rows.DefaultSize = faNV3.Rows.DefaultSize + 5;
            CboConection.SelectedIndex = 0;
            cboXN.SelectedIndex = 0;
            ptgroup = grbHSCN.Location;
            txtMaDVI.Focus();

        }
        private void LoadObjectButton_Click(object sender, System.EventArgs e)
        {
            // Load the customer object from the existing XML file (if any)...
            if (File.Exists(XML_FILE_NGUYENQUAN) == true)
            {
                // Load the customer object from the XML file using our custom class...
                NguyenQuan nguyenquan = ObjectXMLSerializer<NguyenQuan>.Load(XML_FILE_NGUYENQUAN);

                if (nguyenquan == null)
                {
                    MessageBox.Show("Unable to load customer object from file '" + XML_FILE_NGUYENQUAN + "'!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else  // Load customer properties into the form...
                {
                    this.LoadNguyenQuanIntoForm(nguyenquan);
                }
            }
            else
            {
                MessageBox.Show(this.CreateFileDoesNotExistMsg(), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (CheckBeforeAdd() == false)
            {
                MessageBox.Show("Sè bhxh ®• tån t¹i.", "thông báo", MessageBoxButtons.OK);
                return;
            }
            faNV2.Rows.Count = faNV2.Rows.Count + 1;
            faNV2.Rows[faNV2.Rows.Count - 1]["STT"] = faNV2.Rows.Count - 1;
            faNV2.Rows[faNV2.Rows.Count - 1]["Sobhxh"] = faNV1.Rows[faNV1.Selection.TopRow]["Sobhxh"];
            faNV2.Rows[faNV2.Rows.Count - 1]["hoten"] = faNV1.Rows[faNV1.Selection.TopRow]["hoten"];
            faNV2.Rows[faNV2.Rows.Count - 1]["ngaysinh"] = faNV1.Rows[faNV1.Selection.TopRow]["ngaysinh"];
            faNV2.Rows[faNV2.Rows.Count - 1]["gioitinh"] = faNV1.Rows[faNV1.Selection.TopRow]["gioitinh"];
            faNV2.Rows[faNV2.Rows.Count - 1]["dantoc"] = faNV1.Rows[faNV1.Selection.TopRow]["dantoc"];
            faNV2.Rows[faNV2.Rows.Count - 1]["quoctich"] = faNV1.Rows[faNV1.Selection.TopRow]["quoctich"];
            faNV2.Rows[faNV2.Rows.Count - 1]["socmnd"] = faNV1.Rows[faNV1.Selection.TopRow]["socmnd"];
            faNV2.Rows[faNV2.Rows.Count - 1]["NGAYCMND"] = faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"];
            faNV2.Rows[faNV2.Rows.Count - 1]["noicap"] = faNV1.Rows[faNV1.Selection.TopRow]["noicap"];
            faNV2.Rows[faNV2.Rows.Count - 1]["diachi"] = faNV1.Rows[faNV1.Selection.TopRow]["diachi"];
            faNV2.Rows[faNV2.Rows.Count - 1]["noikhai"] = faNV1.Rows[faNV1.Selection.TopRow]["noikhai"];
            faNV2.Rows[faNV2.Rows.Count - 1]["macv"] = faNV1.Rows[faNV1.Selection.TopRow]["macv"];
            faNV2.Rows[faNV2.Rows.Count - 1]["ho"] = faNV1.Rows[faNV1.Selection.TopRow]["ho"];
            faNV2.Rows[faNV2.Rows.Count - 1]["ten"] = faNV1.Rows[faNV1.Selection.TopRow]["ten"];
            faNV2.Rows[faNV2.Rows.Count - 1]["KEYSL"] = faNV1.Rows[faNV1.Selection.TopRow]["KEYSL"];
        }
        private void btnRemove_Click(object sender, EventArgs e)
        {

            if (faNV2.Selection.TopRow > 0)
            {
                faNV2.Rows.Remove(faNV2.Selection.TopRow);
            }
        }
        private void txtgioitinh_TextChanged(object sender, EventArgs e)
        {
            if (txtgioitinh.Text == "0")
            {
                lblgioitinhht.Text = "Nữ";
            }
            else if (txtgioitinh.Text == "1")
            {
                lblgioitinhht.Text = "Nam";
            }
        }
        public void SendKey(Form frm, KeyEventArgs e)
        {

            try
            {

                if (((((e.KeyCode == Keys.Return) & !e.Alt) & !e.Control) & !e.Shift) && !((frm.ActiveControl.Parent is C1.Win.C1FlexGrid.C1FlexGrid) | (frm.ActiveControl is C1.Win.C1FlexGrid.C1FlexGrid)))
                {

                    if (frm.GetNextControl(frm.ActiveControl, true) != null)
                    {
                        SendKeys.Send("{TAB}");
                    }
                    else
                    {
                        while (frm.GetNextControl(frm.ActiveControl, true) == null)
                        {

                            if (!frm.GetNextControl(frm.ActiveControl, true).Enabled)
                            {
                                frm.ActiveControl = frm.GetNextControl(frm.ActiveControl, true);
                            }
                            else
                            {
                                if (!frm.GetNextControl(frm.ActiveControl, true).Visible)
                                {
                                    if (frm.GetNextControl(frm.ActiveControl, true) is TabPage)
                                    {
                                        frm.ActiveControl = frm.GetNextControl(frm.ActiveControl, true);
                                        return;
                                    }
                                    frm.ActiveControl = frm.GetNextControl(frm.ActiveControl, true);
                                    continue;
                                }
                                if (frm.GetNextControl(frm.ActiveControl, true) is GroupBox)
                                {
                                    frm.GetNextControl(frm.ActiveControl, true).Focus();
                                    frm.GetNextControl(frm.ActiveControl, true).Focus();
                                    continue;
                                }
                                if (frm.GetNextControl(frm.ActiveControl, true) is TabControl)
                                {
                                    frm.ActiveControl = frm.GetNextControl(frm.ActiveControl, true);
                                    continue;
                                }
                                if (frm.GetNextControl(frm.ActiveControl, true) is TabPage)
                                {
                                    frm.ActiveControl = frm.GetNextControl(frm.ActiveControl, true);
                                    continue;
                                }
                                if (frm.GetNextControl(frm.ActiveControl, true) is RadioButton)
                                {
                                    if (frm.ActiveControl is RadioButton)
                                    {
                                        SendKeys.Send("{TAB}");
                                    }
                                    else
                                    {
                                        frm.ActiveControl = frm.GetNextControl(frm.ActiveControl, true);
                                    }
                                    return;
                                }
                                if (frm.GetNextControl(frm.ActiveControl, true) is Label)
                                {
                                    frm.GetNextControl(frm.ActiveControl, true).Focus();
                                    frm.GetNextControl(frm.ActiveControl, true).Focus();
                                }
                                else
                                {
                                    frm.GetNextControl(frm.ActiveControl, true).Focus();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception exception1)
            {

                Exception exception = exception1;
                throw exception;

            }
        }
        private void cmdRefresh_Click(object sender, EventArgs e)
        {

            GetInformation();
            Clear();
        }
        private void cmdLuu_Click(object sender, EventArgs e)
        {
            {

                if (txtho.Text == "")
                {
                    MessageBox.Show("nhËp hä");
                    txtho.Focus();
                    return;
                }
                if (txtten.Text == "")
                {
                    MessageBox.Show("NhËp tªn");
                    txtten.Focus();
                    return;
                }
                if (dtngaysinh.Text == "")
                {
                    MessageBox.Show("NhËp ngµy sinh");
                    dtngaysinh.Focus();
                    return;
                }
                if (txtgioitinh.Text == "")
                {
                    MessageBox.Show("NhËp giíi tÝnh");
                    txtgioitinh.Focus();
                    return;
                }
                if (dtngaycap.Text == "")
                {
                    MessageBox.Show("nhËp ngµy cÊp");
                    dtngaycap.Focus();
                    return;
                }
                DateTime temp;
                if (DateTime.TryParse(dtngaycap.Text, out temp) == false)
                {
                    MessageBox.Show("nhËp l¹i ngµy cÊp");
                    dtngaycap.Focus();
                    return;

                }

                if (cbodantoc.SelectedValue == null)
                {
                    MessageBox.Show("nhËp d©n téc");
                    cbodantoc.Focus();
                    return;
                }
                if (cboquoctich.SelectedValue == null)
                {
                    MessageBox.Show("nhËp quèc tÞch");
                    cboquoctich.Focus();
                    return;
                }
                string cmnd = txtCMND.Text + txtCMND1.Text + txtCMND2.Text;
                if (cmnd != "")
                {
                    string sobhxh = CheckCMND(cmnd, strsobhxh);
                    if (sobhxh != "")
                    {
                        if (MessageBox.Show("Số CMND bị trùng với số bhxh " + (sobhxh.Trim()) + " bạn muốn tiếp tục?", "bhxh", MessageBoxButtons.YesNo) == DialogResult.No)
                        {
                            txtCMND.Focus();
                            return;
                        }
                    }
                }
                //check kiem tra so xo bhxh da in so chua
                if (CheckCapSo(strsobhxh) == false) { return; }

                if (cbonguyenquan.Row < 0 && cbonguyenquan.Text != "")
                {
                    NguyenQuan NguyenQuan = this.CreateNguyenQuan();
                    //Save customer object to XML file using our ObjectXMLSerializer class...
                    try
                    {
                        ObjectXMLSerializer<NguyenQuan>.Save(NguyenQuan, XML_FILE_NGUYENQUAN);
                        LoadNguyenQuanIntoForm(NguyenQuan);

                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Unable to save customer object!" + Environment.NewLine + Environment.NewLine + ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

                }
                if (cbocongviec.Row < 0 && cbocongviec.Text != "")
                {
                    CongViec congviec = this.CreateCongViec();
                    //Save customer object to XML file using our ObjectXMLSerializer class...
                    try
                    {
                        ObjectXMLSerializer<CongViec>.Save(congviec, XML_FILE_CONGVIEC);
                        LoadCongViecIntoForm(congviec);

                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Unable to save customer object!" + Environment.NewLine + Environment.NewLine + ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
                UpdateNhanvien();
                UpdateFromControltogrid();
                //SetNextRecode();
                txtho.Focus();
                tmResult.Start();
                lblResult.Text = "Lưu Thành Công";
            }




        }
        private void c1Button1_Click(object sender, EventArgs e)
        {

            for (int i = 1; i < faNV2.Rows.Count; i++)
            {
                if (faNV2.Rows[i]["sobhxhNew"].ToString() == null)
                {
                    MessageBox.Show("nhËp l¹i sè bhxh");
                    faNV2.Select(i, 2);
                    return;
                }
                if (CheckBHXH(faNV2.Rows[i]["sobhxhNew"].ToString()) == false)
                {
                    faNV2.Select(i, 2);
                    return;
                }
            }
            string queryString;
            string sobhxh;
            SqlConnection connection1 = new SqlConnection(connectionString1);
            connection1.Open();
            SqlCommand command1 = connection1.CreateCommand();
            SqlConnection connection2 = new SqlConnection(connectionString2);
            connection2.Open();
            SqlCommand command2 = connection2.CreateCommand();
            using (SqlConnection connection =
                       new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                for (int i = 1; i < faNV2.Rows.Count; i++)
                {

                    sobhxh = faNV2.Rows[i]["sobhxhNew"].ToString();
                    queryString =
                    "exec sp_executesql N'EXEC UPDATE_SOBHXH @P1 , @P2 , @P3 , @P4 , @P5 ',"
                    + " N'@P1 varchar(16),@P2 varchar(10),@P3 varchar(1),@P4 varchar(1),@P5 float', "
                    + "'" + faNV2.Rows[i]["sobhxh"] + "', '" + sobhxh + "', '', '', 1.000000000000000e+000;";
                    command.CommandText = queryString;
                    command.ExecuteNonQuery();

                    queryString =
                          "exec sp_executesql N'EXEC UPDATE_SOBHXH @P1 , @P2, @P3 , @P4 ',"
                          + " N'@P1 varchar(16),@P2 varchar(10),@P3 varchar(10),@P4 varchar(16)', "
                          + "'" + faNV2.Rows[i]["sobhxh"] + "','" + sobhxh
                          + "','" + BHXH_NT.Properties.Settings.Default.DBName
                          + "','" + BHXH_NT.Properties.Settings.Default.DBName_Sothe + "';";

                    command1.CommandText = queryString;
                    command1.ExecuteNonQuery();

                    queryString =
                        "exec sp_executesql N'UPDATE IMAGES SET Sobhxh=@P1"
                        + " WHERE Sobhxh=@P2  ', N'@P1 varchar(10),@P2 varchar(16)',"
                        + "'" + sobhxh + "', '" + faNV2.Rows[i]["sobhxh"] + "';";
                    command2.CommandText = queryString;
                    command2.ExecuteNonQuery();


                }
                GetInformation();
                GetBHXH();
                faNV2.Rows.Count = 1;

            }


        }
        private void txtCMND_TextChanged(object sender, EventArgs e)
        {
            if (txtCMND.Text.Length == 3 && FlagSendkey == true)
            {
                SendKeys.Send("{TAB}");
            }
        }
        private void txtCMND1_TextChanged(object sender, EventArgs e)
        {
            if (txtCMND1.Text.Length == 3 && FlagSendkey == true)
            {
                SendKeys.Send("{TAB}");
            }
        }
        private void txtCMND2_TextChanged(object sender, EventArgs e)
        {
            if (txtCMND2.Text.Length == 3 && FlagSendkey == true)
            {
                SendKeys.Send("{TAB}");
            }
        }
        private void frmHSCN_KeyDown(object sender, KeyEventArgs e)
        {
            SendKey(this, e);
        }
        private void txtCMND_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((Microsoft.VisualBasic.Strings.Asc(e.KeyChar) < 48) | (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) > 57))
            {

                e.Handled = true;
            }
            if (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) == 8)
            {
                e.Handled = false;
            }
        }
        private void txtCMND1_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((Microsoft.VisualBasic.Strings.Asc(e.KeyChar) < 48) | (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) > 57))
            {
                e.Handled = true;
            }
            if (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) == 8)
            {
                e.Handled = false;
            }
        }
        private void txtCMND2_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((Microsoft.VisualBasic.Strings.Asc(e.KeyChar) < 48) | (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) > 57))
            {
                e.Handled = true;
            }
            if (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) == 8)
            {
                e.Handled = false;
            }
        }
        private void txtgioitinh_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((Microsoft.VisualBasic.Strings.Asc(e.KeyChar) == 48) | (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) == 49 | Microsoft.VisualBasic.Strings.Asc(e.KeyChar) == 8))
            {
                e.Handled = false;
            }
            else
            {
                e.Handled = true;
            }
        }
        private void cboNoiCap_TextChanged(object sender, EventArgs e)
        {
            if (cboNoiCap.SelectedValue != null)
            {
                txtNoicap.Text = cboNoiCap.SelectedValue.ToString().Trim();
            }
        }
        private void faNV1_DoubleClick(object sender, EventArgs e)
        {
            btnAdd_Click(sender, e);
        }
        private void btnDvi_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            GetTenDVI();
            GetInformation();
            GetBHXH();
            btnXN.Enabled = false;
            btnXoaGiam.Enabled = false;
            Cursor.Current = Cursors.Default;
        }
        private void faNV1_AfterRowColChange(object sender, C1.Win.C1FlexGrid.RangeEventArgs e)
        {
            if (faNV1.Selection.TopRow >= 1 && faNV1.Rows[faNV1.Selection.TopRow]["ho"] != null)
            {
                Clear();
                FlagSendkey = false;//chan senkey cac texbox cmnd
                txtho.Text = faNV1.Rows[faNV1.Selection.TopRow]["ho"].ToString().Trim();
                txtten.Text = faNV1.Rows[faNV1.Selection.TopRow]["ten"].ToString().Trim();
                txtgioitinh.Text = faNV1.Rows[faNV1.Selection.TopRow]["gioitinh"].ToString().Trim();
                dtngaysinh.Value = Convert.ToDateTime(faNV1.Rows[faNV1.Selection.TopRow]["ngaysinh"]);
                if (faNV1.Rows[faNV1.Selection.TopRow]["dantoc"] != null && faNV1.Rows[faNV1.Selection.TopRow]["dantoc"].ToString().Trim() != "")
                {
                    cbodantoc.SelectedValue = faNV1.Rows[faNV1.Selection.TopRow]["dantoc"];
                }
                else
                {
                    cbodantoc.SelectedValue = "01";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["quoctich"] != null && faNV1.Rows[faNV1.Selection.TopRow]["quoctich"].ToString().Trim() != "")
                {
                    cboquoctich.SelectedValue = faNV1.Rows[faNV1.Selection.TopRow]["quoctich"];
                }
                else
                {
                    cboquoctich.SelectedValue = "VN";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"].ToString().Trim().Length > 2)
                {
                    txtCMND.Text = faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"].ToString().Trim().Substring(0, 3);
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"].ToString().Trim().Length > 4)
                {
                    txtCMND1.Text = faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"].ToString().Trim().Substring(3, 3);
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"].ToString().Trim().Length == 9)
                {
                    txtCMND2.Text = faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"].ToString().Trim().Substring(6, 3);
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"].ToString() != "")
                {
                    dtngaycap.Value = Convert.ToDateTime(faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"]);
                }
                else
                {
                    dtngaycap.Value = DateTime.Now;
                }
                txtNoicap.Text = faNV1.Rows[faNV1.Selection.TopRow]["noicap"].ToString().Trim();
                txtdiachi.Text = faNV1.Rows[faNV1.Selection.TopRow]["diachi"].ToString().Trim();

                txtMaBV.Text = faNV1.Rows[faNV1.Selection.TopRow]["MA_BV"].ToString().Trim();
                txtMaTinh.Text = faNV1.Rows[faNV1.Selection.TopRow]["MA_TINH"].ToString().Trim();

                cbonguyenquan.SelectedValue = faNV1.Rows[faNV1.Selection.TopRow]["noikhai"].ToString().Trim();
                if (cbonguyenquan.SelectedValue == null)
                {
                    cbonguyenquan.Text = faNV1.Rows[faNV1.Selection.TopRow]["noikhai"].ToString().Trim();
                }
                cbocongviec.SelectedValue = faNV1.Rows[faNV1.Selection.TopRow]["macv"];
                if (cbocongviec.SelectedValue == null)
                {
                    cbocongviec.Text = faNV1.Rows[faNV1.Selection.TopRow]["macv"].ToString().Trim();
                }
                cboNoiCap.SelectedValue = 0;
                strsobhxh = faNV1.Rows[faNV1.Selection.TopRow]["Sobhxh"].ToString();
                strsokcb = faNV1.Rows[faNV1.Selection.TopRow]["Sokcb"].ToString();
                strKeyls = faNV1.Rows[faNV1.Selection.TopRow]["KEYSL"].ToString();
                Flaggrid = 1;


            }
        }
        private void faNV3_AfterRowColChange(object sender, C1.Win.C1FlexGrid.RangeEventArgs e)
        {
            if (faNV3.Selection.TopRow >= 1 && faNV3.Rows[faNV3.Selection.TopRow]["ho"] != null)
            {
                Clear();
                FlagSendkey = false;
                txtho.Text = faNV3.Rows[faNV3.Selection.TopRow]["ho"].ToString().Trim();
                txtten.Text = faNV3.Rows[faNV3.Selection.TopRow]["ten"].ToString().Trim();
                txtgioitinh.Text = faNV3.Rows[faNV3.Selection.TopRow]["gioitinh"].ToString().Trim();
                dtngaysinh.Value = Convert.ToDateTime(faNV3.Rows[faNV3.Selection.TopRow]["ngaysinh"]);
                if (faNV3.Rows[faNV3.Selection.TopRow]["dantoc"] != null && faNV3.Rows[faNV3.Selection.TopRow]["dantoc"].ToString().Trim() != "")
                {
                    cbodantoc.SelectedValue = faNV3.Rows[faNV3.Selection.TopRow]["dantoc"];
                }
                else
                {
                    cbodantoc.SelectedValue = "01";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["quoctich"] != null && faNV3.Rows[faNV3.Selection.TopRow]["quoctich"].ToString().Trim() != "")
                {
                    cboquoctich.SelectedValue = faNV3.Rows[faNV3.Selection.TopRow]["quoctich"];
                }
                else
                {
                    cboquoctich.SelectedValue = "VN";
                }

                if (faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString().Trim().Length > 2)
                {
                    txtCMND.Text = faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString().Trim().Substring(0, 3);
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString().Trim().Length > 4)
                {
                    txtCMND1.Text = faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString().Trim().Substring(3, 3);
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString().Trim().Length == 9)
                {
                    txtCMND2.Text = faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString().Trim().Substring(6, 3);
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"].ToString() != "")
                {
                    dtngaycap.Value = Convert.ToDateTime(faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"]);
                }
                else
                {
                    dtngaycap.Value = dtngaycap.Value = DateTime.Now;
                }
                txtNoicap.Text = faNV3.Rows[faNV3.Selection.TopRow]["noicap"].ToString().Trim();
                txtdiachi.Text = faNV3.Rows[faNV3.Selection.TopRow]["diachi"].ToString().Trim();

                txtMaBV.Text = faNV3.Rows[faNV3.Selection.TopRow]["MA_BV"].ToString().Trim();
                txtMaTinh.Text = faNV3.Rows[faNV3.Selection.TopRow]["MA_TINH"].ToString().Trim();

                cbonguyenquan.SelectedValue = faNV3.Rows[faNV3.Selection.TopRow]["noikhai"].ToString().Trim();
                if (cbonguyenquan.SelectedValue == null)
                {
                    cbonguyenquan.Text = faNV3.Rows[faNV3.Selection.TopRow]["noikhai"].ToString().Trim();
                }
                cbocongviec.SelectedValue = faNV3.Rows[faNV3.Selection.TopRow]["macv"];
                if (cbocongviec.SelectedValue == null)
                {
                    cbocongviec.Text = faNV3.Rows[faNV3.Selection.TopRow]["macv"].ToString().Trim();
                }
                cboNoiCap.SelectedValue = 0;
                strsobhxh = faNV3.Rows[faNV3.Selection.TopRow]["Sobhxh"].ToString();
                strsokcb = faNV3.Rows[faNV3.Selection.TopRow]["Sokcb"].ToString();
                strKeyls = faNV3.Rows[faNV3.Selection.TopRow]["KEYSL"].ToString();
                Flaggrid = 3;
            }
        }
        private void txtTinhKCB_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((Microsoft.VisualBasic.Strings.Asc(e.KeyChar) < 48) | (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) > 57))
            {

                e.Handled = true;
            }
            if (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) == 8)
            {
                e.Handled = false;
            }
        }
        private void txtMaBV_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((Microsoft.VisualBasic.Strings.Asc(e.KeyChar) < 48) | (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) > 57))
            {

                e.Handled = true;
            }
            if (Microsoft.VisualBasic.Strings.Asc(e.KeyChar) == 8)
            {
                e.Handled = false;
            }
        }
        private void btnChapNhan_Click(object sender, EventArgs e)
        {
            String queryString;
            string sobhxh = CheckSoBHXH(strsobhxh, "INTHE_CAPLAI");
            if (sobhxh != "")
            {
                queryString = "exec sp_executesql N'Update " + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI set MADVI = @P1,MACL = @P3 where SOBHXH = @p2', "
                     + " N'@P1 char(16),@P2 char(16),@P3 char(2)', "
                     + " '" + txtMaDVI.Text + "A" + "','" + strsobhxh + "', '" + cboLydo.SelectedValue + "'";
            }
            else
            {
                queryString = "exec sp_executesql N'INSERT INTO " + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI (MADVI,SOBHXH,MACL,SOKCB) "
                          + " VALUES (@P1,@P2,@P3,@P4)', "
                          + " N'@P1 char(16),@P2 char(16),@P3 char(2),@P4 char(16)', "
                          + " '" + txtMaDVI.Text + "A" + "','" + strsobhxh + "', '" + cboLydo.SelectedValue + "','" + strsokcb + "'";
            }


            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                command.ExecuteNonQuery();
                tmResult.Start();
                lblResult.Text = "Cập Nhật Thành Công";

            }
        }
        private void CboConection_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (CboConection.SelectedIndex == 0)
            {
                connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
            }
            else
            {
                connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString3;
            }
        }
        private void txtCMND_Enter(object sender, EventArgs e)
        {
            FlagSendkey = true;
        }
        private void txtCMND1_Enter(object sender, EventArgs e)
        {
            FlagSendkey = true;
        }
        private void txtCMND2_Enter(object sender, EventArgs e)
        {
            FlagSendkey = true;
        }
        private void txtPhongtoFa1_Click(object sender, EventArgs e)
        {
            faNV1.BringToFront();
            if (faNV1.Height == 239)
            {
                faNV1.Height = faNV1.Height + faNV3.Height;

            }
            else
            {
                faNV1.Height = 239;
            }



        }
        private void txtPhongtoFa2_Click(object sender, EventArgs e)
        {

            Point p = new Point();
            faNV3.BringToFront();
            p.X = 0;
            p.Y = 0;
            if (faNV3.Location.Y == 269)
            {

                faNV3.Location = faNV1.Location;
                faNV3.Height = faNV3.Height + faNV1.Height;
            }
            else
            {
                p.X = 6;
                p.Y = 269;
                faNV3.Location = p;
                faNV3.Height = faNV3.Height - faNV1.Height;
            }
        }
        private void btnPhongtohscn_Click(object sender, EventArgs e)
        {
            if (grbHSCN.Location != faNV2.Location)
            {
                grbHSCN.Location = faNV2.Location;
            }
            else
            {
                grbHSCN.Location = ptgroup;
            }
        }
        private void btnXoa_Click(object sender, EventArgs e)
        {
            string queryString;
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                queryString = "delete " + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI where SOBHXH = '" + strsobhxh + "'";
                command.CommandText = queryString;
                command.ExecuteNonQuery();
                MessageBox.Show("Xãa thµnh c«ng");
            }
        }
        private void tmResult_Tick(object sender, EventArgs e)
        {

            lblResult.Text = "";
            tmResult.Stop();
        }

        private void btnQuaTrinh_Click(object sender, EventArgs e)
        {
            QuatrinhBHXH dsQuatrinh = new QuatrinhBHXH();
            DataRow newRow = null;
            DataRow oldRow = null;

            int i;
            string sobhxh = "", Maxqt = "";
            if (Flaggrid == 1)
            {
                sobhxh = faNV1.Rows[faNV1.Selection.TopRow]["Sobhxh"].ToString();
            }
            else if (Flaggrid == 3)
            {
                sobhxh = faNV3.Rows[faNV3.Selection.TopRow]["Sobhxh"].ToString();
            }

            string queryString = "exec sp_executesql N'SELECT TOP 1 Maqt, Madvi FROM HSC47_DTL WHERE Sobhxh=@P1  ORDER BY Maqt DESC', N'@P1 varchar(10)','" + sobhxh + "'";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Maxqt = reader["Maqt"].ToString().Substring(0, 6);
                }
            }



            queryString = "exec sp_executesql N'EXEC QUATRINHCN @P1 , @P2 , @P3 ', "
                + " N'@P1 varchar(16),@P2 varchar(6),@P3 varchar(6)', '" + sobhxh + "', '190101', '" + Maxqt + "'";
            faQuaTrinh.Rows.Count = 1;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    newRow = dsQuatrinh.Tables[0].NewRow();
                    if (reader["loaips"].ToString() == "1" && reader["pa"].ToString() != "SB")
                    {

                        newRow["Madvi"] = reader["Madvi"];
                        newRow["Tuthang"] = reader["Maqt"].ToString().Substring(0, 6);
                        newRow["Loaidt"] = reader["Loaidt"];
                        newRow["Ma_nte"] = reader["Ma_nte"];
                        newRow["Mucluong"] = reader["ML"];
                        newRow["Hsl"] = reader["Hsl"];
                        newRow["PCCV"] = reader["pc1"];
                        newRow["PCTN"] = reader["pc2"];
                        newRow["PCNG"] = reader["pc3"];
                        newRow["PCKV"] = reader["pc4"];
                        newRow["pa"] = reader["pa"];
                        newRow["loaips"] = reader["loaips"];

                        if (reader["Congviec"].ToString().Trim() == "")
                        {
                            newRow["Congviec"] = cbocongviec.Text;
                        }
                        else
                        {
                            newRow["Congviec"] = reader["Congviec"];
                        }
                        //sap xep theo maqt
                        for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                        {
                            if (Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i]["Tuthang"].ToString()) > Convert.ToInt32(newRow["Tuthang"].ToString()))
                            {
                                dsQuatrinh.Tables[0].Rows.InsertAt(newRow, i);
                                break;
                            }
                        }
                        if (dsQuatrinh.Tables[0].Rows.Count == i) { dsQuatrinh.Tables[0].Rows.Add(newRow); }
                    }
                    else if (reader["loaips"].ToString() == "2" && reader["pa"].ToString() != "SB") //loai ps=2
                    {
                        newRow["Madvi"] = reader["Madvi"];
                        newRow["Tuthang"] = reader["Tuthang"];
                        newRow["Denthang"] = reader["Denthang"];
                        newRow["Loaidt"] = reader["Loaidt"];
                        newRow["Ma_nte"] = reader["Ma_nte"];
                        newRow["Mucluong"] = reader["ML"];
                        newRow["Hsl"] = reader["Hsl"];
                        newRow["PCCV"] = reader["pc1"];
                        newRow["PCTN"] = reader["pc2"];
                        newRow["PCNG"] = reader["pc3"];
                        newRow["PCKV"] = reader["pc4"];
                        newRow["loaips"] = reader["loaips"];

                        newRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                                Convert.ToDateTime(newRow["Denthang"].ToString().Substring(0, 4) + "/" + newRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                        newRow["pa"] = reader["pa"];
                        if (reader["Congviec"].ToString().Trim() == "")
                        {
                            newRow["Congviec"] = cbocongviec.Text;
                        }
                        else
                        {
                            newRow["Congviec"] = reader["Congviec"];
                        }

                        //sap xep theo maqt
                        for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                        {
                            if (Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i]["Tuthang"].ToString()) > Convert.ToInt32(newRow["Tuthang"].ToString()))
                            {
                                dsQuatrinh.Tables[0].Rows.InsertAt(newRow, i);
                                break;
                            }
                        }
                        if (dsQuatrinh.Tables[0].Rows.Count == i) { dsQuatrinh.Tables[0].Rows.Add(newRow); }


                    }
                }
                reader.Close();
                //chen them donvi cho cac dong ko co ten don vi
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    //tim dong de insert tendenvi
                    if (oldRow["Madvi"].ToString() == txtMaDVI.Text + "A")
                    {
                        oldRow["Tendvi"] = txtTenDV.Text;
                    }
                    else if (oldRow["Madvi"].ToString() != "HA0000A")
                    {
                        queryString = "select Tendvi from DMDONVI WHERE MADVI = '" + oldRow["Madvi"].ToString() + "'";
                        command = connection.CreateCommand();
                        command.CommandText = queryString;
                        reader = command.ExecuteReader();
                        reader.Read();
                        oldRow["Tendvi"] = reader["Tendvi"];
                        reader.Close();
                    }
                }

                //chen them donvi cho cac dong ko co ten don vi nam trong bang history
                queryString = "exec sp_executesql N'SELECT Madvi, Tendvi, Nghenghiep, Loaidt, Tuthang, Denthang, Dk1, Dk2, Dk3, Dk4, Dk5, Dk6 "
                + " FROM HISTORY WHERE Sobhxh=@P1  AND ((@P2  BETWEEN Tuthang AND Denthang) or "
                + " (@P3  BETWEEN Tuthang AND Denthang) OR (@P4 <=Tuthang AND @P5 >=Denthang)) ',"
                + " N'@P1 varchar(10),@P2 varchar(6),@P3 varchar(6),@P4 varchar(6),@P5 varchar(6)', '" + sobhxh + "', '196001', '" + Maxqt + "'"
                + " ,'196001', '" + Maxqt + "'";

                command = connection.CreateCommand();
                command.CommandText = queryString;
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                    {
                        oldRow = dsQuatrinh.Tables[0].Rows[i];
                        //tim dong de insert tendenvi
                        if (oldRow["loaips"].ToString() == "2" && oldRow["DenThang"].ToString() == reader["Denthang"].ToString())
                        {
                            oldRow["Tendvi"] = reader["Tendvi"];
                        }
                    }
                }
                reader.Close();


                //hoan chinh sap xep
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    //them den thang cho nhung thang khong co den thang
                    if (oldRow["DenThang"].ToString() == "")
                    {
                        //dong cuoi cung
                        if (i == dsQuatrinh.Tables[0].Rows.Count - 1)
                        {
                            oldRow["DenThang"] = DateAndTime.Now.Year.ToString() + string.Format("{0:00}", DateAndTime.Now.Month);
                            oldRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                            break;
                        }


                        oldRow["DenThang"] = Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i + 1]["Tuthang"].ToString()) - 1;

                        if (oldRow["DenThang"].ToString().Substring(4) == "00")
                        {
                            oldRow["DenThang"] = Convert.ToInt32(oldRow["DenThang"].ToString().Substring(0, 4)) - 1;
                            oldRow["DenThang"] = oldRow["DenThang"].ToString() + "12";
                        }

                        if (i > 0 && Convert.ToInt32(oldRow["tuthang"]) < Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i - 1]["denThang"]))
                        {
                            oldRow.Delete();
                            i = i - 1;
                        }
                        else
                        {
                            oldRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                                Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                            if (Convert.ToInt32(oldRow["Sothang"]) == 0 || oldRow["Sothang"].ToString() == "")
                            {
                                oldRow.Delete();
                                i = i - 1;
                            }
                        }


                        //check last ps1
                        if (checkLastps1(i, dsQuatrinh) == true && i < dsQuatrinh.Tables[0].Rows.Count - 1)
                        {
                            newRow = dsQuatrinh.Tables[0].NewRow();
                            newRow["Madvi"] = oldRow["Madvi"];
                            newRow["Tuthang"] = Convert.ToInt32(dsQuatrinh.Tables[0].Rows[dsQuatrinh.Tables[0].Rows.Count - 1]["denThang"]) + 1;
                            if (newRow["Tuthang"].ToString().Substring(4) == "13")
                            {
                                newRow["Tuthang"] = Convert.ToInt32(newRow["Tuthang"].ToString().Substring(0, 4)) + 1;
                                newRow["Tuthang"] = newRow["Tuthang"].ToString() + "01";
                            }
                            newRow["Denthang"] = DateAndTime.Now.Year.ToString() + string.Format("{0:00}", DateAndTime.Now.Month);
                            newRow["Loaidt"] = oldRow["Loaidt"];
                            newRow["Ma_nte"] = oldRow["Ma_nte"];
                            newRow["Mucluong"] = oldRow["Mucluong"];
                            newRow["Hsl"] = oldRow["Hsl"];
                            newRow["PCCV"] = oldRow["PCCV"];
                            newRow["PCTN"] = oldRow["PCTN"];
                            newRow["PCNG"] = oldRow["PCNG"];
                            newRow["PCKV"] = oldRow["PCKV"];
                            newRow["pa"] = oldRow["pa"];
                            newRow["Congviec"] = oldRow["Congviec"];
                            newRow["Tendvi"] = oldRow["Tendvi"];
                            newRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                              Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                            dsQuatrinh.Tables[0].Rows.Add(newRow);
                        }

                    }
                }
                //them dong de hoan thanh qua trinh
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count - 1; i++)
                {

                    if (dsQuatrinh.Tables[0].Rows[i]["loaips"].ToString() == "1")
                    {
                        oldRow = dsQuatrinh.Tables[0].Rows[i];
                    }

                    if (DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(dsQuatrinh.Tables[0].Rows[i]["Denthang"].ToString().Substring(0, 4) + "/" + dsQuatrinh.Tables[0].Rows[i]["Denthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(dsQuatrinh.Tables[0].Rows[i + 1]["Tuthang"].ToString().Substring(0, 4) + "/" + dsQuatrinh.Tables[0].Rows[i + 1]["Tuthang"].ToString().Substring(4, 2) + "/01")) > 1 && dsQuatrinh.Tables[0].Rows[i]["pa"].ToString().Trim() != "")
                    {



                        newRow = dsQuatrinh.Tables[0].NewRow();
                        newRow["Madvi"] = oldRow["Madvi"];
                        newRow["Tuthang"] = Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i]["denThang"]) + 1;
                        if (newRow["Tuthang"].ToString().Substring(4) == "13")
                        {
                            newRow["Tuthang"] = Convert.ToInt32(newRow["Tuthang"].ToString().Substring(0, 4)) + 1;
                            newRow["Tuthang"] = newRow["Tuthang"].ToString() + "01";
                        }
                        newRow["DenThang"] = Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i + 1]["Tuthang"].ToString()) - 1;

                        if (newRow["DenThang"].ToString().Substring(4) == "00")
                        {
                            newRow["DenThang"] = Convert.ToInt32(newRow["DenThang"].ToString().Substring(0, 4)) - 1;
                            newRow["DenThang"] = newRow["DenThang"].ToString() + "12";
                        }

                        newRow["Loaidt"] = oldRow["Loaidt"];
                        newRow["Ma_nte"] = oldRow["Ma_nte"];
                        newRow["Mucluong"] = oldRow["Mucluong"];
                        newRow["Hsl"] = oldRow["Hsl"];
                        newRow["PCCV"] = oldRow["PCCV"];
                        newRow["PCTN"] = oldRow["PCTN"];
                        newRow["PCNG"] = oldRow["PCNG"];
                        newRow["PCKV"] = oldRow["PCKV"];
                        newRow["pa"] = oldRow["pa"];
                        newRow["Congviec"] = oldRow["Congviec"];
                        newRow["Tendvi"] = oldRow["Tendvi"];
                        newRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                          Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                        dsQuatrinh.Tables[0].Rows.InsertAt(newRow, i + 1);
                        i++;


                    }


                }
                //merge
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count - 1; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    newRow = dsQuatrinh.Tables[0].Rows[i + 1];
                    if (oldRow["Denthang"] is System.DBNull) { break; }
                    if (DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01")) == 1)
                    {

                        if (oldRow["Mucluong"].ToString() == newRow["Mucluong"].ToString() &&
                            oldRow["HSL"].ToString() == newRow["HSL"].ToString() &&
                             oldRow["PCCV"].ToString() == newRow["PCCV"].ToString() &&
                             oldRow["PCTN"].ToString() == newRow["PCTN"].ToString() &&
                             oldRow["PCNG"].ToString() == newRow["PCNG"].ToString() &&
                             oldRow["PCKV"].ToString() == newRow["PCKV"].ToString() &&
                             oldRow["PCCV"].ToString() == newRow["PCCV"].ToString() &&
                             oldRow["Congviec"].ToString().ToUpper().Trim() == newRow["Congviec"].ToString().ToUpper().Trim() &&
                             oldRow["Tendvi"].ToString() == newRow["Tendvi"].ToString())
                        {
                            newRow["Tuthang"] = oldRow["Tuthang"];
                            newRow["Sothang"] = Convert.ToInt32(newRow["Sothang"]) + Convert.ToInt32(oldRow["Sothang"]);
                            oldRow.Delete();
                            i = i - 1;
                        }
                    }
                    //Xoa dong trung
                    else if (Convert.ToInt32(oldRow["tuThang"]) >= Convert.ToInt32(newRow["tuThang"]) && Convert.ToInt32(oldRow["denThang"]) <= Convert.ToInt32(newRow["denThang"]))
                    {
                        oldRow.Delete();
                        i = i - 1;
                    }
                }
                //cat theo ti le bhxh
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    if (oldRow["Denthang"] is System.DBNull) { break; }
                    if (Convert.ToInt32(oldRow["denThang"].ToString()) > 200912 && Convert.ToInt32(oldRow["tuThang"].ToString()) < 200912 && oldRow["pa"].ToString() != "GH")
                    {

                        newRow = dsQuatrinh.Tables[0].NewRow();
                        newRow["Madvi"] = oldRow["Madvi"];
                        newRow["Tuthang"] = oldRow["Tuthang"];
                        newRow["Denthang"] = oldRow["Denthang"];
                        newRow["Loaidt"] = oldRow["Loaidt"];
                        newRow["Ma_nte"] = oldRow["Ma_nte"];
                        newRow["Mucluong"] = oldRow["Mucluong"];
                        newRow["Hsl"] = oldRow["Hsl"];
                        newRow["PCCV"] = oldRow["PCCV"];
                        newRow["PCTN"] = oldRow["PCTN"];
                        newRow["PCNG"] = oldRow["PCNG"];
                        newRow["PCKV"] = oldRow["PCKV"];
                        newRow["pa"] = oldRow["pa"];
                        newRow["Congviec"] = oldRow["Congviec"];
                        newRow["Tendvi"] = oldRow["Tendvi"];
                        oldRow["denthang"] = "200912";
                        oldRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                              Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;

                        newRow["tuThang"] = "201001";
                        newRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                              Convert.ToDateTime(newRow["Denthang"].ToString().Substring(0, 4) + "/" + newRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;

                        dsQuatrinh.Tables[0].Rows.InsertAt(newRow, i + 1);
                        break;
                    }
                }
                //them dong khong tham gia và cắt các dòng có thời gian trung nhau
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count - 1; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    newRow = dsQuatrinh.Tables[0].Rows[i + 1];
                    //cat cac dong co thoi gian trung
                    if (DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01")) <= 0)
                    {
                        oldRow["Denthang"] = Convert.ToInt32(newRow["Tuthang"]) - 1;
                        if (oldRow["DenThang"].ToString().Substring(4) == "00")
                        {
                            oldRow["DenThang"] = Convert.ToInt32(oldRow["DenThang"].ToString().Substring(0, 4)) - 1;
                            oldRow["DenThang"] = oldRow["DenThang"].ToString() + "12";
                        }
                        oldRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                             Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;

                    }
                    //them dong ko tham gia
                    else if (DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01")) > 1)
                    {
                        DataRow ktgRow = dsQuatrinh.Tables[0].NewRow();
                        //newRow["Madvi"] = oldRow["Madvi"];
                        ktgRow["Tuthang"] = Convert.ToInt32(oldRow["denThang"]) + 1;
                        if (ktgRow["Tuthang"].ToString().Substring(4) == "13")
                        {
                            ktgRow["Tuthang"] = Convert.ToInt32(ktgRow["Tuthang"].ToString().Substring(0, 4)) + 1;
                            ktgRow["Tuthang"] = ktgRow["Tuthang"].ToString() + "01";
                        }
                        ktgRow["DenThang"] = Convert.ToInt32(newRow["Tuthang"].ToString()) - 1;

                        if (ktgRow["DenThang"].ToString().Substring(4) == "00")
                        {
                            ktgRow["DenThang"] = Convert.ToInt32(ktgRow["DenThang"].ToString().Substring(0, 4)) - 1;
                            ktgRow["DenThang"] = ktgRow["DenThang"].ToString() + "12";
                        }
                        ktgRow["Mucluong"] = 0;
                        ktgRow["Hsl"] = 0;
                        ktgRow["PCCV"] = 0;
                        ktgRow["PCTN"] = 0;
                        ktgRow["PCNG"] = 0;
                        ktgRow["PCKV"] = 0;

                        ktgRow["pa"] = "OF";

                        ktgRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(ktgRow["Tuthang"].ToString().Substring(0, 4) + "/" + ktgRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                          Convert.ToDateTime(ktgRow["Denthang"].ToString().Substring(0, 4) + "/" + ktgRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                        dsQuatrinh.Tables[0].Rows.InsertAt(ktgRow, i + 1);


                    }
                }
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                {
                    faQuaTrinh.Rows.Count = faQuaTrinh.Rows.Count + 1;
                    faQuaTrinh.Rows[i + 1]["Madvi"] = dsQuatrinh.Tables[0].Rows[i]["Madvi"];
                    faQuaTrinh.Rows[i + 1]["TuThang"] = ModuleBHXH.FormatThangNam(dsQuatrinh.Tables[0].Rows[i]["Tuthang"].ToString());
                    faQuaTrinh.Rows[i + 1]["DenThang"] = ModuleBHXH.FormatThangNam(dsQuatrinh.Tables[0].Rows[i]["DenThang"].ToString());



                    // faQuaTrinh.Rows[i + 1]["TuThang"] = dsQuatrinh.Tables[0].Rows[i]["Tuthang"].ToString().Substring(4, 2) + "/" + dsQuatrinh.Tables[0].Rows[i]["Tuthang"].ToString().Substring(0, 4);
                    // faQuaTrinh.Rows[i + 1]["DenThang"] = dsQuatrinh.Tables[0].Rows[i]["DenThang"].ToString().Substring(4, 2) + "/" + dsQuatrinh.Tables[0].Rows[i]["DenThang"].ToString().Substring(0, 4);

                    faQuaTrinh.Rows[i + 1]["Loai"] = dsQuatrinh.Tables[0].Rows[i]["Loaidt"];
                    faQuaTrinh.Rows[i + 1]["LoaiTT"] = dsQuatrinh.Tables[0].Rows[i]["Ma_nte"];
                    if (Convert.ToDouble(dsQuatrinh.Tables[0].Rows[i]["MucLuong"]) != 0) { faQuaTrinh.Rows[i + 1]["MucLuong"] = dsQuatrinh.Tables[0].Rows[i]["Mucluong"]; }
                    if (Convert.ToDouble(dsQuatrinh.Tables[0].Rows[i]["HSL"]) != 0) { faQuaTrinh.Rows[i + 1]["HSL"] = dsQuatrinh.Tables[0].Rows[i]["HSL"]; }
                    if (Convert.ToDouble(dsQuatrinh.Tables[0].Rows[i]["PCCV"]) != 0) { faQuaTrinh.Rows[i + 1]["PCCV"] = dsQuatrinh.Tables[0].Rows[i]["PCCV"]; }
                    if (Convert.ToDouble(dsQuatrinh.Tables[0].Rows[i]["PCTN"]) != 0) { faQuaTrinh.Rows[i + 1]["PCTN"] = dsQuatrinh.Tables[0].Rows[i]["PCTN"]; }
                    if (Convert.ToDouble(dsQuatrinh.Tables[0].Rows[i]["PCNG"]) != 0) { faQuaTrinh.Rows[i + 1]["PCNG"] = dsQuatrinh.Tables[0].Rows[i]["PCNG"]; }
                    if (Convert.ToDouble(dsQuatrinh.Tables[0].Rows[i]["PCKV"]) != 0) { faQuaTrinh.Rows[i + 1]["PCKV"] = dsQuatrinh.Tables[0].Rows[i]["PCKV"]; }

                    faQuaTrinh.Rows[i + 1]["Sothang"] = dsQuatrinh.Tables[0].Rows[i]["Sothang"];
                    faQuaTrinh.Rows[i + 1]["pa"] = dsQuatrinh.Tables[0].Rows[i]["pa"];
                    if (faQuaTrinh.Rows[i + 1]["pa"].ToString() == "GH" || faQuaTrinh.Rows[i + 1]["pa"].ToString() == "OF"
                        || faQuaTrinh.Rows[i + 1]["pa"].ToString() == "GD")
                    {
                        C1.Win.C1FlexGrid.CellStyle rs = this.faQuaTrinh.Styles.Add("rowcolor");
                        rs.BackColor = Color.Orange;
                        faQuaTrinh.Rows[i + 1].Style = rs;

                    }
                    faQuaTrinh.Rows[i + 1]["Congviec"] = dsQuatrinh.Tables[0].Rows[i]["Congviec"];
                    faQuaTrinh.Rows[i + 1]["Tendvi"] = dsQuatrinh.Tables[0].Rows[i]["Tendvi"];

                }
            }
        }
        private void btnInquatrinh_Click(object sender, EventArgs e)
        {
            QuatrinhBHXH dsQuatrinh = new QuatrinhBHXH();
            DataRow newRow = null;
            DataRow oldRow = null;

            int i;
            string sobhxh = "", Maxqt = "";
            if (Flaggrid == 1)
            {
                sobhxh = faNV1.Rows[faNV1.Selection.TopRow]["Sobhxh"].ToString();
            }
            else if (Flaggrid == 3)
            {
                sobhxh = faNV3.Rows[faNV3.Selection.TopRow]["Sobhxh"].ToString();
            }

            string queryString = "exec sp_executesql N'SELECT TOP 1 Maqt, Madvi FROM HSC47_DTL WHERE Sobhxh=@P1  ORDER BY Maqt DESC', N'@P1 varchar(10)','" + sobhxh + "'";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Maxqt = reader["Maqt"].ToString().Substring(0, 6);
                }
            }



            queryString = "exec sp_executesql N'EXEC QUATRINHCN @P1 , @P2 , @P3 ', "
                + " N'@P1 varchar(16),@P2 varchar(6),@P3 varchar(6)', '" + sobhxh + "', '190101', '" + Maxqt + "'";
            faQuaTrinh.Rows.Count = 1;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    newRow = dsQuatrinh.Tables[0].NewRow();
                    if (reader["loaips"].ToString() == "1" && reader["pa"].ToString() != "SB")
                    {

                        newRow["Madvi"] = reader["Madvi"];
                        newRow["Tuthang"] = reader["Maqt"].ToString().Substring(0, 6);
                        newRow["Loaidt"] = reader["Loaidt"];
                        newRow["Ma_nte"] = reader["Ma_nte"];
                        newRow["Mucluong"] = reader["ML"];
                        newRow["Hsl"] = reader["Hsl"];
                        newRow["PCCV"] = reader["pc1"];
                        newRow["PCTN"] = reader["pc2"];
                        newRow["PCNG"] = reader["pc3"];
                        newRow["PCKV"] = reader["pc4"];
                        newRow["pa"] = reader["pa"];
                        newRow["loaips"] = reader["loaips"];

                        if (reader["Congviec"].ToString().Trim() == "")
                        {
                            newRow["Congviec"] = cbocongviec.Text;
                        }
                        else
                        {
                            newRow["Congviec"] = reader["Congviec"];
                        }
                        //sap xep theo maqt
                        for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                        {
                            if (Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i]["Tuthang"].ToString()) > Convert.ToInt32(newRow["Tuthang"].ToString()))
                            {
                                dsQuatrinh.Tables[0].Rows.InsertAt(newRow, i);
                                break;
                            }
                        }
                        if (dsQuatrinh.Tables[0].Rows.Count == i) { dsQuatrinh.Tables[0].Rows.Add(newRow); }
                    }
                    else if (reader["loaips"].ToString() == "2" && reader["pa"].ToString() != "SB") //loai ps=2
                    {
                        newRow["Madvi"] = reader["Madvi"];
                        newRow["Tuthang"] = reader["Tuthang"];
                        newRow["Denthang"] = reader["Denthang"];
                        newRow["Loaidt"] = reader["Loaidt"];
                        newRow["Ma_nte"] = reader["Ma_nte"];
                        newRow["Mucluong"] = reader["ML"];
                        newRow["Hsl"] = reader["Hsl"];
                        newRow["PCCV"] = reader["pc1"];
                        newRow["PCTN"] = reader["pc2"];
                        newRow["PCNG"] = reader["pc3"];
                        newRow["PCKV"] = reader["pc4"];
                        newRow["loaips"] = reader["loaips"];

                        newRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                                Convert.ToDateTime(newRow["Denthang"].ToString().Substring(0, 4) + "/" + newRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                        newRow["pa"] = reader["pa"];
                        if (reader["Congviec"].ToString().Trim() == "")
                        {
                            newRow["Congviec"] = cbocongviec.Text;
                        }
                        else
                        {
                            newRow["Congviec"] = reader["Congviec"];
                        }

                        //sap xep theo maqt
                        for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                        {
                            if (Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i]["Tuthang"].ToString()) > Convert.ToInt32(newRow["Tuthang"].ToString()))
                            {
                                dsQuatrinh.Tables[0].Rows.InsertAt(newRow, i);
                                break;
                            }
                        }
                        if (dsQuatrinh.Tables[0].Rows.Count == i) { dsQuatrinh.Tables[0].Rows.Add(newRow); }


                    }
                }
                reader.Close();
                //chen them donvi cho cac dong ko co ten don vi
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    //tim dong de insert tendenvi
                    if (oldRow["Madvi"].ToString() == txtMaDVI.Text + "A")
                    {
                        oldRow["Tendvi"] = txtTenDV.Text;
                    }
                    else if (oldRow["Madvi"].ToString() != "HA0000A")
                    {
                        queryString = "select Tendvi from DMDONVI WHERE MADVI = '" + oldRow["Madvi"].ToString() + "'";
                        command = connection.CreateCommand();
                        command.CommandText = queryString;
                        reader = command.ExecuteReader();
                        reader.Read();
                        oldRow["Tendvi"] = reader["Tendvi"];
                        reader.Close();
                    }
                }

                //chen them donvi cho cac dong ko co ten don vi nam trong bang history
                queryString = "exec sp_executesql N'SELECT Madvi, Tendvi, Nghenghiep, Loaidt, Tuthang, Denthang, Dk1, Dk2, Dk3, Dk4, Dk5, Dk6 "
                + " FROM HISTORY WHERE Sobhxh=@P1  AND ((@P2  BETWEEN Tuthang AND Denthang) or "
                + " (@P3  BETWEEN Tuthang AND Denthang) OR (@P4 <=Tuthang AND @P5 >=Denthang)) ',"
                + " N'@P1 varchar(10),@P2 varchar(6),@P3 varchar(6),@P4 varchar(6),@P5 varchar(6)', '" + sobhxh + "', '196001', '" + Maxqt + "'"
                + " ,'196001', '" + Maxqt + "'";

                command = connection.CreateCommand();
                command.CommandText = queryString;
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                    {
                        oldRow = dsQuatrinh.Tables[0].Rows[i];
                        //tim dong de insert tendenvi
                        if (oldRow["loaips"].ToString() == "2" && oldRow["DenThang"].ToString() == reader["Denthang"].ToString())
                        {
                            oldRow["Tendvi"] = reader["Tendvi"];
                        }
                    }
                }
                reader.Close();


                //hoan chinh sap xep
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    //them den thang cho nhung thang khong co den thang
                    if (oldRow["DenThang"].ToString() == "")
                    {
                        //dong cuoi cung
                        if (i == dsQuatrinh.Tables[0].Rows.Count - 1)
                        {
                            oldRow["DenThang"] = DateAndTime.Now.Year.ToString() + string.Format("{0:00}", DateAndTime.Now.Month);
                            oldRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                            break;
                        }


                        oldRow["DenThang"] = Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i + 1]["Tuthang"].ToString()) - 1;

                        if (oldRow["DenThang"].ToString().Substring(4) == "00")
                        {
                            oldRow["DenThang"] = Convert.ToInt32(oldRow["DenThang"].ToString().Substring(0, 4)) - 1;
                            oldRow["DenThang"] = oldRow["DenThang"].ToString() + "12";
                        }

                        if (i > 0 && Convert.ToInt32(oldRow["tuthang"]) < Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i - 1]["denThang"]))
                        {
                            oldRow.Delete();
                            i = i - 1;
                        }
                        else
                        {
                            oldRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                                Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                            if (Convert.ToInt32(oldRow["Sothang"]) == 0 || oldRow["Sothang"].ToString() == "")
                            {
                                oldRow.Delete();
                                i = i - 1;
                            }
                        }


                        //check last ps1
                        if (checkLastps1(i, dsQuatrinh) == true && i < dsQuatrinh.Tables[0].Rows.Count - 1)
                        {
                            newRow = dsQuatrinh.Tables[0].NewRow();
                            newRow["Madvi"] = oldRow["Madvi"];
                            newRow["Tuthang"] = Convert.ToInt32(dsQuatrinh.Tables[0].Rows[dsQuatrinh.Tables[0].Rows.Count - 1]["denThang"]) + 1;
                            if (newRow["Tuthang"].ToString().Substring(4) == "13")
                            {
                                newRow["Tuthang"] = Convert.ToInt32(newRow["Tuthang"].ToString().Substring(0, 4)) + 1;
                                newRow["Tuthang"] = newRow["Tuthang"].ToString() + "01";
                            }
                            newRow["Denthang"] = DateAndTime.Now.Year.ToString() + string.Format("{0:00}", DateAndTime.Now.Month);
                            newRow["Loaidt"] = oldRow["Loaidt"];
                            newRow["Ma_nte"] = oldRow["Ma_nte"];
                            newRow["Mucluong"] = oldRow["Mucluong"];
                            newRow["Hsl"] = oldRow["Hsl"];
                            newRow["PCCV"] = oldRow["PCCV"];
                            newRow["PCTN"] = oldRow["PCTN"];
                            newRow["PCNG"] = oldRow["PCNG"];
                            newRow["PCKV"] = oldRow["PCKV"];
                            newRow["pa"] = oldRow["pa"];
                            newRow["Congviec"] = oldRow["Congviec"];
                            newRow["Tendvi"] = oldRow["Tendvi"];
                            newRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                              Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                            dsQuatrinh.Tables[0].Rows.Add(newRow);
                        }

                    }
                }
                //them dong de hoan thanh qua trinh
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count - 1; i++)
                {

                    if (dsQuatrinh.Tables[0].Rows[i]["loaips"].ToString() == "1")
                    {
                        oldRow = dsQuatrinh.Tables[0].Rows[i];
                    }

                    if (DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(dsQuatrinh.Tables[0].Rows[i]["Denthang"].ToString().Substring(0, 4) + "/" + dsQuatrinh.Tables[0].Rows[i]["Denthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(dsQuatrinh.Tables[0].Rows[i + 1]["Tuthang"].ToString().Substring(0, 4) + "/" + dsQuatrinh.Tables[0].Rows[i + 1]["Tuthang"].ToString().Substring(4, 2) + "/01")) > 1 && dsQuatrinh.Tables[0].Rows[i]["pa"].ToString().Trim() != "")
                    {



                        newRow = dsQuatrinh.Tables[0].NewRow();
                        newRow["Madvi"] = oldRow["Madvi"];
                        newRow["Tuthang"] = Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i]["denThang"]) + 1;
                        if (newRow["Tuthang"].ToString().Substring(4) == "13")
                        {
                            newRow["Tuthang"] = Convert.ToInt32(newRow["Tuthang"].ToString().Substring(0, 4)) + 1;
                            newRow["Tuthang"] = newRow["Tuthang"].ToString() + "01";
                        }
                        newRow["DenThang"] = Convert.ToInt32(dsQuatrinh.Tables[0].Rows[i + 1]["Tuthang"].ToString()) - 1;

                        if (newRow["DenThang"].ToString().Substring(4) == "00")
                        {
                            newRow["DenThang"] = Convert.ToInt32(newRow["DenThang"].ToString().Substring(0, 4)) - 1;
                            newRow["DenThang"] = newRow["DenThang"].ToString() + "12";
                        }
                        newRow["Loaidt"] = oldRow["Loaidt"];
                        newRow["Ma_nte"] = oldRow["Ma_nte"];
                        newRow["Mucluong"] = oldRow["Mucluong"];
                        newRow["Hsl"] = oldRow["Hsl"];
                        newRow["PCCV"] = oldRow["PCCV"];
                        newRow["PCTN"] = oldRow["PCTN"];
                        newRow["PCNG"] = oldRow["PCNG"];
                        newRow["PCKV"] = oldRow["PCKV"];
                        newRow["pa"] = oldRow["pa"];
                        newRow["Congviec"] = oldRow["Congviec"];
                        newRow["Tendvi"] = oldRow["Tendvi"];
                        newRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                          Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                        dsQuatrinh.Tables[0].Rows.InsertAt(newRow, i + 1);
                        i++;

                    }
                }
                //merge
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count - 1; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    newRow = dsQuatrinh.Tables[0].Rows[i + 1];
                    if (oldRow["Denthang"] is System.DBNull) { break; }
                    if (DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01")) == 1)
                    {

                        if (oldRow["Mucluong"].ToString() == newRow["Mucluong"].ToString() &&
                            oldRow["HSL"].ToString() == newRow["HSL"].ToString() &&
                             oldRow["PCCV"].ToString() == newRow["PCCV"].ToString() &&
                             oldRow["PCTN"].ToString() == newRow["PCTN"].ToString() &&
                             oldRow["PCNG"].ToString() == newRow["PCNG"].ToString() &&
                             oldRow["PCKV"].ToString() == newRow["PCKV"].ToString() &&
                             oldRow["PCCV"].ToString() == newRow["PCCV"].ToString() &&
                             oldRow["Congviec"].ToString().ToUpper().Trim() == newRow["Congviec"].ToString().ToUpper().Trim() &&
                             oldRow["Tendvi"].ToString() == newRow["Tendvi"].ToString())
                        {
                            newRow["Tuthang"] = oldRow["Tuthang"];
                            newRow["Sothang"] = Convert.ToInt32(newRow["Sothang"]) + Convert.ToInt32(oldRow["Sothang"]);
                            oldRow.Delete();
                            i = i - 1;
                        }
                    }
                    //Xoa dong trung
                    else if (Convert.ToInt32(oldRow["tuThang"]) >= Convert.ToInt32(newRow["tuThang"]) && Convert.ToInt32(oldRow["denThang"]) <= Convert.ToInt32(newRow["denThang"]))
                    {
                        oldRow.Delete();
                        i = i - 1;
                    }
                }
                //cat theo ti le bhxh
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    if (oldRow["Denthang"] is System.DBNull) { break; }
                    if (Convert.ToInt32(oldRow["denThang"].ToString()) > 200912 && Convert.ToInt32(oldRow["tuThang"].ToString()) < 200912 && oldRow["pa"].ToString() != "GH")
                    {

                        newRow = dsQuatrinh.Tables[0].NewRow();
                        newRow["Madvi"] = oldRow["Madvi"];
                        newRow["Tuthang"] = oldRow["Tuthang"];
                        newRow["Denthang"] = oldRow["Denthang"];
                        newRow["Loaidt"] = oldRow["Loaidt"];
                        newRow["Ma_nte"] = oldRow["Ma_nte"];
                        newRow["Mucluong"] = oldRow["Mucluong"];
                        newRow["Hsl"] = oldRow["Hsl"];
                        newRow["PCCV"] = oldRow["PCCV"];
                        newRow["PCTN"] = oldRow["PCTN"];
                        newRow["PCNG"] = oldRow["PCNG"];
                        newRow["PCKV"] = oldRow["PCKV"];
                        newRow["pa"] = oldRow["pa"];
                        newRow["Congviec"] = oldRow["Congviec"];
                        newRow["Tendvi"] = oldRow["Tendvi"];
                        oldRow["denthang"] = "200912";
                        oldRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                              Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;

                        newRow["tuThang"] = "201001";
                        newRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                              Convert.ToDateTime(newRow["Denthang"].ToString().Substring(0, 4) + "/" + newRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;

                        dsQuatrinh.Tables[0].Rows.InsertAt(newRow, i + 1);
                        break;
                    }
                }
                //them dong khong tham gia và cắt các dòng có thời gian trung nhau
                for (i = 0; i < dsQuatrinh.Tables[0].Rows.Count - 1; i++)
                {
                    oldRow = dsQuatrinh.Tables[0].Rows[i];
                    newRow = dsQuatrinh.Tables[0].Rows[i + 1];
                    //cat cac dong co thoi gian trung
                    if (DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01")) <= 0)
                    {
                        oldRow["Denthang"] = Convert.ToInt32(newRow["Tuthang"]) - 1;
                        if (oldRow["DenThang"].ToString().Substring(4) == "00")
                        {
                            oldRow["DenThang"] = Convert.ToInt32(oldRow["DenThang"].ToString().Substring(0, 4)) - 1;
                            oldRow["DenThang"] = oldRow["DenThang"].ToString() + "12";
                        }
                        oldRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Tuthang"].ToString().Substring(0, 4) + "/" + oldRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                             Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;

                    }
                    //them dong ko tham gia
                    else if (DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(oldRow["Denthang"].ToString().Substring(0, 4) + "/" + oldRow["Denthang"].ToString().Substring(4, 2) + "/01"),
                            Convert.ToDateTime(newRow["Tuthang"].ToString().Substring(0, 4) + "/" + newRow["Tuthang"].ToString().Substring(4, 2) + "/01")) > 1)
                    {
                        DataRow ktgRow = dsQuatrinh.Tables[0].NewRow();
                        //newRow["Madvi"] = oldRow["Madvi"];
                        ktgRow["Tuthang"] = Convert.ToInt32(oldRow["denThang"]) + 1;
                        if (ktgRow["Tuthang"].ToString().Substring(4) == "13")
                        {
                            ktgRow["Tuthang"] = Convert.ToInt32(ktgRow["Tuthang"].ToString().Substring(0, 4)) + 1;
                            ktgRow["Tuthang"] = ktgRow["Tuthang"].ToString() + "01";
                        }
                        ktgRow["DenThang"] = Convert.ToInt32(newRow["Tuthang"].ToString()) - 1;

                        if (ktgRow["DenThang"].ToString().Substring(4) == "00")
                        {
                            ktgRow["DenThang"] = Convert.ToInt32(ktgRow["DenThang"].ToString().Substring(0, 4)) - 1;
                            ktgRow["DenThang"] = ktgRow["DenThang"].ToString() + "12";
                        }
                        ktgRow["Mucluong"] = 0;
                        ktgRow["Hsl"] = 0;
                        ktgRow["PCCV"] = 0;
                        ktgRow["PCTN"] = 0;
                        ktgRow["PCNG"] = 0;
                        ktgRow["PCKV"] = 0;
                        ktgRow["pa"] = "OF";
                        ktgRow["Sothang"] = DateAndTime.DateDiff(DateInterval.Month, Convert.ToDateTime(ktgRow["Tuthang"].ToString().Substring(0, 4) + "/" + ktgRow["Tuthang"].ToString().Substring(4, 2) + "/01"),
                          Convert.ToDateTime(ktgRow["Denthang"].ToString().Substring(0, 4) + "/" + ktgRow["Denthang"].ToString().Substring(4, 2) + "/01")) + 1;
                        dsQuatrinh.Tables[0].Rows.InsertAt(ktgRow, i + 1);


                    }
                }
            }

        }
        private void btnNV_Click(object sender, EventArgs e)
        {
            btnXN.Enabled = true;
            btnXoaGiam.Enabled = true;
            GetInformationGiam();
            GetBHXHGIAM();
        }
        private void btnXN_Click(object sender, EventArgs e)
        {
            String queryString;
            string sobhxh = CheckSoBHXH(strsobhxh, "INTR_BL");
            if (sobhxh != "")
            {
                queryString = "exec sp_executesql N'Update " + Settings.Default.DBName_Sothe + ".dbo.INTR_BL set MADVI = @P1,XACNHAN = @P3 where SOBHXH = @p2', "
                     + " N'@P1 char(16),@P2 char(16),@P3 char(2)', "
                     + " '" + txtMaDVI.Text + "A" + "','" + strsobhxh + "', '" + cboXN.Text + "'";
            }
            else
            {
                queryString = "exec sp_executesql N'INSERT INTO " + Settings.Default.DBName_Sothe + ".dbo.INTR_BL (MADVI,SOBHXH,XACNHAN) "
                          + " VALUES (@P1,@P2,@P3)', "
                          + " N'@P1 char(16),@P2 char(16),@P3 char(2)', "
                          + " '" + txtMaDVI.Text + "A" + "','" + strsobhxh + "', '" + cboXN.Text + "'";
            }


            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                command.ExecuteNonQuery();
                tmResult.Start();
                lblResult.Text = "Cập Nhật Thành Công";

            }

        }
        private void btnXoaGiam_Click(object sender, EventArgs e)
        {
            string queryString;
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                queryString = "delete " + Settings.Default.DBName_Sothe + ".dbo.INTR_BL where SOBHXH = '" + strsobhxh + "'";
                command.CommandText = queryString;
                command.ExecuteNonQuery();
                MessageBox.Show("Xãa thµnh c«ng");
            }
        }
        private void btnIn_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpDSNV.rpt");
            cr.SetDataSource(dsDSNV);
            f.crpView.ReportSource = cr;
            f.Show();
            Cursor.Current = Cursors.Default;
        }
        private void btnInAll_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            danhsachthe dsDSNVALL = new danhsachthe();
            string queryString = "SELECT RIGHT(RTRIM(a.SOBHXH),6) AS TAM,"
            + " a.Sobhxh, a.Maqt, b.Ml, b.Mlyt, b.Hsl, b.Pc1, b.Pc2, b.Pc3, b.Pc4, "
            + " b.Mdbhyt, b.Pa,  b.Congviec, c.Ho, c.Ten, c.Sokcb, c.Ngaysinh, "
            + " c.Gioitinh, c.Dantoc, c.Quoctich, c.Socmnd, c.Ngaycmnd,   "
            + " c.Noicap, c.Noikhai, c.Noicapso, c.Mavung, c.Diachi, c.DiachiHK, "
            + " c.Macv, c.Mapb, c.Madt, c.Ma_Tinh, c.Ma_bv,  c.Ngayduyet, c.Lancap, "
            + " b.Madvi,d.TenDvi, c.Ngayps, c.Ma_Quan, c.Keysl  "
            + " FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh "
            + " left join DMDONVI d on d.Madvi = b.Madvi ,   "
            + " (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt "
            + " FROM HSC47_DTL WHERE LEFT(Maqt,6)=Denthang AND  "
            + " LEFT(Maqt,6)<= " + ModuleBHXH.strMonths + " AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + " GROUP BY Sobhxh, Loaidt, Ma_Nte) a   "
            + " WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            + " AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang  "
            + " AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt<>0  "
            + " AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + " AND CHARINDEX('TA',a.SOBHXH) = 1 "
            + " AND LEFT(d.MADVI,1) <> 'B' AND d.DSNV like '%" + ModuleBHXH.strUser + "%'"
            + " order by c.Ten";

            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                SqlDataAdapter myAdapter = new SqlDataAdapter();
                command.CommandText = queryString;
                command.CommandTimeout = 60;
                connection.Open();
                command.ExecuteNonQuery();
                myAdapter.SelectCommand = command;
                myAdapter.Fill(dsDSNVALL.dsNhanVien);
            }
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpDSNV.rpt");
            cr.SetDataSource(dsDSNVALL);
            f.crpView.ReportSource = cr;
            f.Show();
            Cursor.Current = Cursors.Default;

        }
        #endregion

        #region "Method Private"
        private void InsertRow(int Tuthang, int Denthang, QuatrinhBHXH dsQuatring)
        {

            for (int i = 0; i < dsQuatring.Tables[0].Rows.Count; i++)
            {
                if (Denthang < Convert.ToInt32(dsQuatring.Tables[0].Rows[i]["tuThang"].ToString()))
                {

                }
            }
        }
        Boolean checkLastps1(int i, QuatrinhBHXH dsQuatrinh)
        {
            for (int j = i + 1; j < dsQuatrinh.Tables[0].Rows.Count; j++)
            {
                if (dsQuatrinh.Tables[0].Rows[j]["DenThang"].ToString() == "")
                {
                    return false;
                }
            }
            return true;
        }
        private string CreateFileDoesNotExistMsg()
        {
            return "The example XML file '" + XML_FILE_NGUYENQUAN + "' does not exist." + "\n\n" +
            "To create the example XML file, enter customer details then click the 'Save' button.";
        }
        private string CreateHowToMsg()
        {
            return "To demonstrate saving of a 'Customer' object to an XML file ('"
                + XML_FILE_NGUYENQUAN + "'), enter the customer details then click the 'Save' button.";
        }
        private Boolean CheckBeforeAdd()
        {
            for (int i = 0; i < faNV2.Rows.Count; i++)
            {
                if (faNV2.Rows[i]["Sobhxh"] == faNV1.Rows[faNV1.Selection.TopRow]["Sobhxh"])
                {
                    return false;
                }
            }
            return true;
        }
        private void UpdateNhanvien()
        {

            string queryString;
            if (Convert.ToDateTime(dtngaycap.Value).ToShortDateString() == DateTime.Today.ToShortDateString()) //truong hop khong thay doi ngay cap
            {
                queryString =
                   "UPDATE HOSOCN SET "
                   + " HO = '" + txtho.Text + "', TEN = '" + txtten.Text + "', NGAYSINH = '" + dtngaysinh.Value.ToString().Substring(6, 4) + "/" + dtngaysinh.Value.ToString().Substring(3, 2) + "/" + dtngaysinh.Value.ToString().Substring(0, 2) + "',"
                   + " GIOITINH = '" + txtgioitinh.Text + "',DANTOC = '" + cbodantoc.SelectedValue + "', QUOCTICH = '" + cboquoctich.SelectedValue + "',"
                   + " SOCMND = '" + txtCMND.Text + txtCMND1.Text + txtCMND2.Text + "', NOICAP = '" + txtNoicap.Text + "',"
                   + " MA_TINH = '" + txtMaTinh.Text + "', MA_BV = '" + txtMaBV.Text + "',"
                   + " DIACHI = '" + txtdiachi.Text + "',NOIKHAI = '" + cbonguyenquan.Text + "',MACV = '" + cbocongviec.Text + "'"
                   + " where SOBHXH = '" + strsobhxh + "' AND KEYSL = '" + strKeyls + "';";
            }
            else
            {
                queryString =
                "UPDATE HOSOCN SET "
                + " HO = '" + txtho.Text + "', TEN = '" + txtten.Text + "', NGAYSINH = '" + dtngaysinh.Value.ToString().Substring(6, 4) + "/" + dtngaysinh.Value.ToString().Substring(3, 2) + "/" + dtngaysinh.Value.ToString().Substring(0, 2) + "',"
                + " GIOITINH = '" + txtgioitinh.Text + "',DANTOC = '" + cbodantoc.SelectedValue + "', QUOCTICH = '" + cboquoctich.SelectedValue + "',"
                + " SOCMND = '" + txtCMND.Text + txtCMND1.Text + txtCMND2.Text + "', NOICAP = '" + txtNoicap.Text + "',NGAYCMND = '" + dtngaycap.Value.ToString().Substring(6, 4) + "/" + dtngaycap.Value.ToString().Substring(3, 2) + "/" + dtngaycap.Value.ToString().Substring(0, 2) + "',"
                + " MA_TINH = '" + txtMaTinh.Text + "', MA_BV = '" + txtMaBV.Text + "',"
                + " DIACHI = '" + txtdiachi.Text + "',NOIKHAI = '" + cbonguyenquan.Text + "',MACV = '" + cbocongviec.Text + "'"
                + " where SOBHXH = '" + strsobhxh + "' AND KEYSL = '" + strKeyls + "';";
            }



            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                command.ExecuteNonQuery();
            }
        }
        private string CheckCMND(string cmnd, string sobhxh)
        {
            string queryString = "select * from hosocn where SOCMND = '" + cmnd + "' and sobhxh <> '" + sobhxh + "';";
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows == true)
                {
                    reader.Read();
                    return reader["SOBHXH"].ToString();
                }
                else
                {
                    return "";
                }
            }

        }
        private string CheckSoBHXH(string sobhxh, string table)
        {
            string queryString = "select * from " + Settings.Default.DBName_Sothe + ".dbo." + table + " where  sobhxh = '" + sobhxh + "';";
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows == true)
                {
                    reader.Read();
                    return reader["SOBHXH"].ToString();
                }
                else
                {
                    return "";
                }
            }

        }
        private void SetNextRecode()
        {
            if (Flaggrid == 1)
            {

                if (faNV1.Selection.BottomRow < faNV1.Rows.Count - 1)
                {
                    faNV1.Select(faNV1.Selection.BottomRow + 1, 2);
                }
                else
                {
                    faNV1.Select(1, 2);
                }
            }
            else
            {
                if (faNV3.Selection.BottomRow < faNV3.Rows.Count - 1)
                {
                    faNV3.Select(faNV3.Selection.BottomRow + 1, 2);
                }
                else
                {
                    faNV3.Select(1, 2);
                }
            }

        }
        public struct HSCN
        {
            public string ho, ten,ngaysinh,dantoc,quoctich,cmnd,ngaycmnd,noicap,diachi,noikhai,matinh,mabv,gioitinh,hoten;

            public HSCN(string tsho,string  tsten,string tsngaysinh,string tsdantoc,string tsquoctich,string tscmnd,
                string tsngaycmnd, string tsnoicap, string tsdiachi, string tsnoikhai, string tsmatinh, string tsmabv, string tsgioitinh,string tshoten)
            {
                ho = "NULL";
                ten= "NULL";
                ngaysinh= "NULL";
                dantoc= "NULL";
                quoctich= "NULL";
                cmnd= "NULL";
                ngaycmnd= "NULL";
                noicap= "NULL";
                diachi= "NULL";
                noikhai= "NULL";
                matinh= "NULL";
                mabv = "NULL";
                gioitinh = "NULL";
                hoten = "NULL";

            }
        }
        private string ReturnNull(string Value)
        {
            Value = Value.Trim();
            if (Value == "")
            {
                Value = "NULL";
            }
            else
            {
                Value = "'" + Value + "'";
            }
            return Value;
        }
        private void InsertHCHSCN()
        {
            string queryString;
            string soct="";
            string keysl = "";
            bool flag = false;
            HSCN hscncu = new HSCN("NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL");
            HSCN hscnmoi = new HSCN("NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL");

            queryString = "SELECT Hchosocn.LOAI, Hchosocn.SOCT, Hchosocn.NGAY, Hchosocn.SOBHXH, "
            + " Hchosocn.MADVI, Hchosocn.THANG, Hchosocn.HOTEN0, Hchosocn.GIOITINH0, Hchosocn.NGAYSINH0, "
            + " Hchosocn.NOIKHAI0, Hchosocn.DANTOC0, Hchosocn.QUOCTICH0, Hchosocn.DIACHIHK0, Hchosocn.DIACHI0, "
            + " Hchosocn.SOCMND0, Hchosocn.NGAYCMND0, Hchosocn.NOICAP0, Hchosocn.HOTEN, Hchosocn.GIOITINH, "
            + " Hchosocn.NGAYSINH, Hchosocn.NOIKHAI, Hchosocn.DANTOC, Hchosocn.QUOCTICH, Hchosocn.DIACHIHK, "
            + " Hchosocn.DIACHI, Hchosocn.SOCMND, Hchosocn.NGAYCMND, Hchosocn.NOICAP, Hchosocn.USERCODE, "
            + " Hchosocn.GHICHU, Hchosocn.KEYSL, Hchosocn.MA_TINH0, Hchosocn.MA_BV0, Hchosocn.MADT0, "
            + " Hchosocn.MA_TINH, Hchosocn.MA_BV, Hchosocn.MADT FROM  dbo.HCHOSOCN Hchosocn WHERE  "
            + " Hchosocn.NGAY = '" + BHXH_NT.ModuleBHXH.strDate + "' and sobhxh = '" + strsobhxh + "'";
            using (SqlConnection connection =
                 new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                SqlDataReader dr = command.ExecuteReader();
                dr.Read();
                if (dr.HasRows == true)
                {
                    flag = true;
                    soct = dr["SOCT"].ToString();
                    keysl = dr["Keysl"].ToString();
                    hscncu.hoten = ReturnNull(dr["HOTEN0"].ToString());
                    hscncu.gioitinh = ReturnNull(dr["GIOITINH0"].ToString());
                    hscncu.ngaysinh = ReturnNull(dr["NGAYSINH0"].ToString());
                    hscncu.noikhai = ReturnNull(dr["NOIKHAI0"].ToString());
                    hscncu.dantoc = ReturnNull(dr["DANTOC0"].ToString());
                    hscncu.quoctich = ReturnNull(dr["QUOCTICH0"].ToString());
                    hscncu.diachi = ReturnNull(dr["DIACHI0"].ToString());
                    hscncu.cmnd = ReturnNull(dr["SOCMND0"].ToString());
                    hscncu.ngaycmnd = ReturnNull(dr["NGAYCMND0"].ToString());
                    hscncu.noicap = ReturnNull(dr["NOICAP0"].ToString());
                    hscncu.matinh = ReturnNull(dr["MA_TINH0"].ToString());
                    hscncu.mabv  = ReturnNull(dr["MA_BV0"].ToString());

                    hscnmoi.hoten = ReturnNull(dr["HOTEN"].ToString());
                    hscnmoi.gioitinh = ReturnNull(dr["GIOITINH"].ToString());
                    hscnmoi.ngaysinh = ReturnNull(dr["NGAYSINH"].ToString());
                    hscnmoi.noikhai = ReturnNull(dr["NOIKHAI"].ToString());
                    hscnmoi.dantoc = ReturnNull(dr["DANTOC"].ToString());
                    hscnmoi.quoctich = ReturnNull(dr["QUOCTICH"].ToString());
                    hscnmoi.diachi = ReturnNull(dr["DIACHI"].ToString());
                    hscnmoi.cmnd = ReturnNull(dr["SOCMND"].ToString());
                    hscnmoi.ngaycmnd = ReturnNull(dr["NGAYCMND"].ToString());
                    hscnmoi.noicap = ReturnNull(dr["NOICAP"].ToString());
                    hscnmoi.matinh  = ReturnNull(dr["MA_TINH"].ToString());
                    hscnmoi.mabv = ReturnNull(dr["MA_BV"].ToString());
                    dr.Close();


                }

            }
            if (Flaggrid == 1)
            {
                if (faNV1.Rows[faNV1.Selection.TopRow]["ho"].ToString().Trim() != txtho.Text || faNV1.Rows[faNV1.Selection.TopRow]["ten"].ToString().Trim() != txtten.Text)
                {
                    if (hscncu.hoten == "NULL")
                    {
                        hscncu.ho = faNV1.Rows[faNV1.Selection.TopRow]["ho"].ToString();
                        hscncu.ten = faNV1.Rows[faNV1.Selection.TopRow]["ten"].ToString();
                        hscncu.hoten = "'" + hscncu.ho + " " + hscncu.ten + "'";
                    }
                    hscnmoi.ho = txtho.Text;
                    hscnmoi.ten = txtten.Text;
                    hscnmoi.hoten = "'" + hscnmoi.ho + " " + hscnmoi.ten + "'";
                }
                if (Convert.ToDateTime(faNV1.Rows[faNV1.Selection.TopRow]["ngaysinh"]).ToShortDateString() != dtngaysinh.Value.ToString())
                {
                    if (hscncu.ngaysinh == "NULL")
                    {
                        hscncu.ngaysinh = "'" + faNV1.Rows[faNV1.Selection.TopRow]["ngaysinh"].ToString().Substring(6, 4) + "/" + faNV1.Rows[faNV1.Selection.TopRow]["ngaysinh"].ToString().Substring(3, 2) + "/" + faNV1.Rows[faNV1.Selection.TopRow]["ngaysinh"].ToString().Substring(0, 2) + "'";
                    }
                    else
                    {
                        hscncu.ngaysinh = "'" + hscncu.ngaysinh.ToString().Substring(7, 4) + "/" + hscncu.ngaysinh.ToString().Substring(4, 2) + "/" + hscncu.ngaysinh.ToString().Substring(1, 2) + "'";
                    }
                    hscnmoi.ngaysinh = "'" + dtngaysinh.Value.ToString().Substring(6, 4) + "/" + dtngaysinh.Value.ToString().Substring(3, 2) + "/" + dtngaysinh.Value.ToString().Substring(0, 2) + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["dantoc"].ToString().Trim() != cbodantoc.SelectedValue.ToString())
                {
                    if (hscncu.dantoc == "NULL")
                    {
                        hscncu.dantoc = "'" + faNV1.Rows[faNV1.Selection.TopRow]["dantoc"].ToString() + "'";
                    }
                    
                    hscnmoi.dantoc = "'" + cbodantoc.SelectedValue.ToString() + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["quoctich"].ToString().Trim() != cboquoctich.SelectedValue.ToString())
                {
                    if (hscncu.quoctich == "NULL")
                    {
                        hscncu.quoctich = "'" + faNV1.Rows[faNV1.Selection.TopRow]["quoctich"].ToString() + "'";
                    }
                   
                    hscnmoi.quoctich = "'" + cboquoctich.SelectedValue.ToString() + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"].ToString().Trim() != txtCMND.Text + txtCMND1.Text + txtCMND2.Text)
                {
                    if (hscncu.cmnd == "NULL")
                    {
                        hscncu.cmnd = "'" + faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"].ToString() + "'";
                    }
                  
                    hscnmoi.cmnd = "'" + txtCMND.Text + txtCMND1.Text + txtCMND2.Text + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"].ToString() != "" && Convert.ToDateTime(dtngaycap.Value).ToShortDateString() != DateTime.Today.ToShortDateString() && Convert.ToDateTime(faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"]).ToShortDateString() != dtngaycap.Value.ToString())
                {
                    if (hscncu.ngaycmnd == "NULL")
                    {
                        hscncu.ngaycmnd = "'" + faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"].ToString().Substring(6, 4) + "/" + faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"].ToString().Substring(3, 2) + "/" + faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"].ToString().Substring(0, 2) + "'";
                    }
                    else
                    {
                        hscncu.ngaycmnd = "'" + hscncu.ngaycmnd.ToString().Substring(7, 4) + "/" + hscncu.ngaycmnd.ToString().Substring(4, 2) + "/" + hscncu.ngaycmnd.ToString().Substring(1, 2) + "'";
                    }
                    hscnmoi.ngaycmnd = "'" + dtngaycap.Value.ToString().Substring(6, 4) + "/" + dtngaycap.Value.ToString().Substring(3, 2) + "/" + dtngaycap.Value.ToString().Substring(0, 2) + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["noicap"].ToString().Trim() != txtNoicap.Text)
                {
                    if (hscncu.noicap == "NULL")
                    {
                        hscncu.noicap = "'" + faNV1.Rows[faNV1.Selection.TopRow]["noicap"].ToString() + "'";
                    }
                    hscnmoi.noicap = "'" + txtNoicap.Text + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["diachi"].ToString().Trim() != txtdiachi.Text)
                {
                    if (hscncu.diachi == "NULL")
                    {
                        hscncu.diachi = "'" + faNV1.Rows[faNV1.Selection.TopRow]["diachi"].ToString() + "'";
                    }
                    hscnmoi.diachi = "'" + txtdiachi.Text + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["noikhai"].ToString().Trim() != cbonguyenquan.Text)
                {
                    if (hscncu.noikhai == "NULL")
                    {
                        hscncu.noikhai = "'" + faNV1.Rows[faNV1.Selection.TopRow]["noikhai"].ToString() + "'";
                    }
                    hscnmoi.noikhai = "'" + cbonguyenquan.Text + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["MA_TINH"].ToString().Trim() != txtMaTinh.Text)
                {
                    if (hscncu.matinh == "NULL")
                    {
                        hscncu.matinh = "'" + faNV1.Rows[faNV1.Selection.TopRow]["MA_TINH"].ToString() + "'";
                    }
                    
                    hscnmoi.matinh = "'" + txtMaTinh.Text + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["MA_BV"].ToString().Trim() != txtMaBV.Text)
                {
                    if (hscncu.mabv == "NULL")
                    {
                        hscncu.mabv = "'" + faNV1.Rows[faNV1.Selection.TopRow]["MA_BV"].ToString() + "'";
                    }
                   
                    hscnmoi.mabv = "'" + txtMaBV.Text + "'";
                }
                if (faNV1.Rows[faNV1.Selection.TopRow]["gioitinh"].ToString().Trim() != txtgioitinh.Text)
                {
                    if (hscncu.gioitinh == "NULL")
                    {
                        hscncu.gioitinh = "'" + faNV1.Rows[faNV1.Selection.TopRow]["gioitinh"].ToString() + "'";
                    }
                    
                    hscnmoi.gioitinh = "'" + txtgioitinh.Text + "'";
                }
            }
            else
            {
                if (faNV3.Rows[faNV3.Selection.TopRow]["ho"].ToString().Trim() != txtho.Text || faNV3.Rows[faNV3.Selection.TopRow]["ten"].ToString().Trim() != txtten.Text)
                {
                    if (hscncu.hoten == "NULL")
                    {
                        hscncu.ho = faNV3.Rows[faNV3.Selection.TopRow]["ho"].ToString();
                        hscncu.ten = faNV3.Rows[faNV3.Selection.TopRow]["ten"].ToString();
                        hscncu.hoten = "'" + hscncu.ho + " " + hscncu.ten + "'";
                    }
                    hscnmoi.ho = txtho.Text;
                    hscnmoi.ten = txtten.Text;
                    hscnmoi.hoten = "'" + hscnmoi.ho + " " + hscnmoi.ten + "'";
                }
                if (Convert.ToDateTime(faNV3.Rows[faNV3.Selection.TopRow]["ngaysinh"]).ToShortDateString() != dtngaysinh.Value.ToString())
                {
                    if (hscncu.ngaysinh == "NULL")
                    {
                        hscncu.ngaysinh = "'" + faNV3.Rows[faNV3.Selection.TopRow]["ngaysinh"].ToString().Substring(6, 4) + "/" + faNV3.Rows[faNV3.Selection.TopRow]["ngaysinh"].ToString().Substring(3, 2) + "/" + faNV3.Rows[faNV3.Selection.TopRow]["ngaysinh"].ToString().Substring(0, 2) + "'";
                    }
                    else
                    {
                        hscncu.ngaysinh = "'" + hscncu.ngaysinh.ToString().Substring(7, 4) + "/" + hscncu.ngaysinh.ToString().Substring(4, 2) + "/" + hscncu.ngaysinh.ToString().Substring(1, 2) + "'";
                    }
                    hscnmoi.ngaysinh = "'" + dtngaysinh.Value.ToString().Substring(6, 4) + "/" + dtngaysinh.Value.ToString().Substring(3, 2) + "/" + dtngaysinh.Value.ToString().Substring(0, 2) + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["dantoc"].ToString().Trim() != cbodantoc.SelectedValue.ToString())
                {
                    if (hscncu.dantoc == "NULL")
                    {
                        hscncu.dantoc = "'" + faNV3.Rows[faNV3.Selection.TopRow]["dantoc"].ToString() + "'";
                    }

                    hscnmoi.dantoc = "'" + cbodantoc.SelectedValue.ToString() + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["quoctich"].ToString().Trim() != cboquoctich.SelectedValue.ToString())
                {
                    if (hscncu.quoctich == "NULL")
                    {
                        hscncu.quoctich = "'" + faNV3.Rows[faNV3.Selection.TopRow]["quoctich"].ToString() + "'";
                    }

                    hscnmoi.quoctich = "'" + cboquoctich.SelectedValue.ToString() + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString().Trim() != txtCMND.Text + txtCMND1.Text + txtCMND2.Text)
                {
                    if (hscncu.cmnd == "NULL")
                    {
                        hscncu.cmnd = "'" + faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString() + "'";
                    }

                    hscnmoi.cmnd = "'" + txtCMND.Text + txtCMND1.Text + txtCMND2.Text + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"].ToString() != "" && Convert.ToDateTime(dtngaycap.Value).ToShortDateString() != DateTime.Today.ToShortDateString() && Convert.ToDateTime(faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"]).ToShortDateString() != dtngaycap.Value.ToString())
                {
                    if (hscncu.ngaycmnd == "NULL")
                    {
                        hscncu.ngaycmnd = "'" + faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"].ToString().Substring(6, 4) + "/" + faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"].ToString().Substring(3, 2) + "/" + faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"].ToString().Substring(0, 2) + "'";
                    }
                    else
                    {
                        hscncu.ngaycmnd = "'" + hscncu.ngaycmnd.ToString().Substring(7, 4) + "/" + hscncu.ngaycmnd.ToString().Substring(4, 2) + "/" + hscncu.ngaycmnd.ToString().Substring(1, 2) + "'";
                    }
                    hscnmoi.ngaycmnd = "'" + dtngaycap.Value.ToString().Substring(6, 4) + "/" + dtngaycap.Value.ToString().Substring(3, 2) + "/" + dtngaycap.Value.ToString().Substring(0, 2) + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["noicap"].ToString().Trim() != txtNoicap.Text)
                {
                    if (hscncu.noicap == "NULL")
                    {
                        hscncu.noicap = "'" + faNV3.Rows[faNV3.Selection.TopRow]["noicap"].ToString() + "'";
                    }
                    hscnmoi.noicap = "'" + txtNoicap.Text + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["diachi"].ToString().Trim() != txtdiachi.Text)
                {
                    if (hscncu.diachi == "NULL")
                    {
                        hscncu.diachi = "'" + faNV3.Rows[faNV3.Selection.TopRow]["diachi"].ToString() + "'";
                    }
                    hscnmoi.diachi = "'" + txtdiachi.Text + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["noikhai"].ToString().Trim() != cbonguyenquan.Text)
                {
                    if (hscncu.noikhai == "NULL")
                    {
                        hscncu.noikhai = "'" + faNV3.Rows[faNV3.Selection.TopRow]["noikhai"].ToString() + "'";
                    }
                    hscnmoi.noikhai = "'" + cbonguyenquan.Text + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["MA_TINH"].ToString().Trim() != txtMaTinh.Text)
                {
                    if (hscncu.matinh == "NULL")
                    {
                        hscncu.matinh = "'" + faNV3.Rows[faNV3.Selection.TopRow]["MA_TINH"].ToString() + "'";
                    }

                    hscnmoi.matinh = "'" + txtMaTinh.Text + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["MA_BV"].ToString().Trim() != txtMaBV.Text)
                {
                    if (hscncu.mabv == "NULL")
                    {
                        hscncu.mabv = "'" + faNV3.Rows[faNV3.Selection.TopRow]["MA_BV"].ToString() + "'";
                    }

                    hscnmoi.mabv = "'" + txtMaBV.Text + "'";
                }
                if (faNV3.Rows[faNV3.Selection.TopRow]["gioitinh"].ToString().Trim() != txtgioitinh.Text)
                {
                    if (hscncu.gioitinh == "NULL")
                    {
                        hscncu.gioitinh = "'" + faNV3.Rows[faNV3.Selection.TopRow]["gioitinh"].ToString() + "'";
                    }

                    hscnmoi.gioitinh = "'" + txtgioitinh.Text + "'";
                }
            }
            if (flag == false)
            {
                queryString = " exec sp_executesql N'SELECT MAX(Soct) As Soct FROM HCHOSOCN WHERE YEAR(Ngay)=@P1 ', N'@P1 float', 2.012000000000000e+003";
                
                using (SqlConnection connection =
                      new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = queryString;
                    SqlDataReader dr = command.ExecuteReader();
                    dr.Read();
                    soct = dr["Soct"].ToString();
                    dr.Close();
                    soct = string.Format("{0:000000}", Convert.ToInt32(soct) + 1);

                }
                queryString = " exec sp_executesql N'INSERT INTO dbo.HCHOSOCN (LOAI,SOCT,NGAY,SOBHXH,MADVI,THANG,HOTEN0,GIOITINH0,"
                     + " NGAYSINH0,NOIKHAI0,DANTOC0,DIACHIHK0,DIACHI0,SOCMND0,NGAYCMND0,NOICAP0,HOTEN,GIOITINH,NGAYSINH,NOIKHAI,"
                     + " DANTOC,DIACHIHK,DIACHI,SOCMND,NGAYCMND,NOICAP,USERCODE,GHICHU,KEYSL,MA_TINH0,MA_BV0,MADT0,MA_TINH,MA_BV,MADT) "
                     + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,"
                     + " @P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35)', N'@P1 char(2),@P2 char(10),"
                     + " @P3 smalldatetime,@P4 char(16),@P5 char(7),@P6 char(6),@P7 varchar(40),@P8 int,@P9 smalldatetime,@P10 varchar(50),"
                     + " @P11 char(2),@P12 varchar(100),@P13 varchar(100),@P14 char(10),@P15 smalldatetime,@P16 varchar(30),@P17 varchar(40),"
                     + " @P18 int,@P19 smalldatetime,@P20 varchar(50),@P21 char(2),@P22 varchar(100),@P23 varchar(100),@P24 char(10),"
                     + " @P25 smalldatetime,@P26 varchar(30),@P27 varchar(10),@P28 varchar(200),@P29 char(20),@P30 char(2),@P31 char(3),"
                     + " @P32 char(2),@P33 char(2),@P34 char(3),@P35 char(2)', "
                     + " 'NB', '" + soct + "', '" + BHXH_NT.ModuleBHXH.strDate + "', '" + strsobhxh + "', "
                     + "'" + txtMaDVI.Text + "A', '" + ModuleBHXH.strMonths.Substring(0, 4) + ModuleBHXH.strMonths.Substring(4, 2) + "', " + hscncu.hoten + " ,"
                     + hscncu.gioitinh + ",  " + hscncu.ngaysinh + ", " + hscncu.noikhai + ", " + hscncu.dantoc + ", NULL, " + hscncu.diachi + ", "
                     + hscncu.cmnd + ", " + hscncu.ngaycmnd + ", " + hscncu.noicap + ", " + hscnmoi.hoten + " ,"
                     + hscnmoi.gioitinh + ",  " + hscnmoi.ngaysinh + ", " + hscnmoi.noikhai + ", " + hscnmoi.dantoc + ", NULL, " + hscnmoi.diachi + ", "
                     + hscnmoi.cmnd + ", " + hscnmoi.ngaycmnd + ", " + hscnmoi.noicap + ", '" + ModuleBHXH.strUser + "', '', "
                     + " '" + ModuleBHXH.CreateRunninNo(rnd) + "', " + hscncu.matinh + ", " + hscncu.mabv + ", NULL, " + hscnmoi.matinh + ", " + hscnmoi.mabv + ", NULL";
            }
            else
            {
                queryString = " exec sp_executesql N'UPDATE dbo.HCHOSOCN SET LOAI=@P1,SOCT=@P2,NGAY=@P3,MADVI=@P4,THANG=@P5,HOTEN0=@P6,"
                + " GIOITINH0=@P7,NGAYSINH0=@P8,NOIKHAI0=@P9,DANTOC0=@P10,DIACHIHK0=@P11,DIACHI0=@P12,SOCMND0=@P13,"
                + " NGAYCMND0=@P14,NOICAP0=@P15,HOTEN=@P16,GIOITINH=@P17,NGAYSINH=@P18,NOIKHAI=@P19,DANTOC=@P20,"
                + " DIACHIHK=@P21,DIACHI=@P22,SOCMND=@P23,NGAYCMND=@P24,NOICAP=@P25,GHICHU=@P26,MA_TINH0=@P27,"
                + " MA_BV0=@P28,MADT0=@P29,MA_TINH=@P30,MA_BV=@P31,MADT=@P32 WHERE NGAY=@P33 AND SOBHXH=@P34 AND KEYSL=@P35', "
                + " N'@P1 char(2),@P2 char(10),@P3 smalldatetime,@P4 char(7),@P5 char(6),@P6 varchar(40),@P7 int,"
                + " @P8 smalldatetime,@P9 varchar(50),@P10 char(2),@P11 varchar(100),@P12 varchar(100),@P13 char(10),"
                + " @P14 smalldatetime,@P15 varchar(30),@P16 varchar(40),@P17 int,@P18 smalldatetime,@P19 varchar(50),"
                + " @P20 char(2),@P21 varchar(100),@P22 varchar(100),@P23 char(10),@P24 smalldatetime,@P25 varchar(30),"
                + " @P26 varchar(200),@P27 char(2),@P28 char(3),@P29 char(2),@P30 char(2),@P31 char(3),@P32 char(2),"
                + " @P33 smalldatetime,@P34 char(16),@P35 char(20)', "
                + " 'NB', '" + soct + "', '" + BHXH_NT.ModuleBHXH.strDate + "', "
                + "'" + txtMaDVI.Text + "A', '" + ModuleBHXH.strMonths.Substring(0, 4) + ModuleBHXH.strMonths.Substring(4, 2) + "', "
                + hscncu.hoten + "," + hscncu.gioitinh + " , " + hscncu.ngaysinh + ", " + hscncu.noikhai + ",  " + hscncu.dantoc + ", NULL, " + hscncu.diachi + ", "
                + hscncu.cmnd + ", " + hscncu.ngaycmnd + ", " + hscncu.noicap + ", " + hscnmoi.hoten + " ,"
                + hscnmoi.gioitinh + ", " + hscnmoi.ngaysinh + ", " + hscnmoi.noikhai + ", " + hscnmoi.dantoc + ", NULL, " + hscnmoi.diachi + ", "
                + hscnmoi.cmnd + ", " + hscnmoi.ngaycmnd + ", " + hscnmoi.noicap + ",'', "
                + hscncu.matinh + ", " + hscncu.mabv + ", NULL, " + hscnmoi.matinh + ", " + hscnmoi.mabv + ", NULL, '" + BHXH_NT.ModuleBHXH.strDate + "', '" + strsobhxh + "', '" + keysl + "'";
            }

            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                command.ExecuteNonQuery();
            }
                
        }
        private void UpdateFromControltogrid()
        {

            InsertHCHSCN();                        
            if (Flaggrid == 1)
            {
                faNV1.Rows[faNV1.Selection.TopRow]["ho"] = txtho.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["ten"] = txtten.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["ngaysinh"] = dtngaysinh.Value;
                faNV1.Rows[faNV1.Selection.TopRow]["dantoc"] = cbodantoc.SelectedValue;
                faNV1.Rows[faNV1.Selection.TopRow]["quoctich"] = cboquoctich.SelectedValue;
                faNV1.Rows[faNV1.Selection.TopRow]["SOCMND"] = txtCMND.Text + txtCMND1.Text + txtCMND2.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["NGAYCMND"] = dtngaycap.Value;
                faNV1.Rows[faNV1.Selection.TopRow]["noicap"] = txtNoicap.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["diachi"] = txtdiachi.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["noikhai"] = cbonguyenquan.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["MA_TINH"] = txtMaTinh.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["MA_BV"] = txtMaBV.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["macv"] = cbocongviec.Text;
                faNV1.Rows[faNV1.Selection.TopRow]["gioitinh"] = txtgioitinh.Text;
            }
            else
            {
                faNV3.Rows[faNV3.Selection.TopRow]["ho"] = txtho.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["ten"] = txtten.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["ngaysinh"] = dtngaysinh.Value;
                faNV3.Rows[faNV3.Selection.TopRow]["dantoc"] = cbodantoc.SelectedValue;
                faNV3.Rows[faNV3.Selection.TopRow]["quoctich"] = cboquoctich.SelectedValue;
                faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"] = txtCMND.Text + txtCMND1.Text + txtCMND2.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"] = dtngaycap.Value;
                faNV3.Rows[faNV3.Selection.TopRow]["noicap"] = txtNoicap.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["diachi"] = txtdiachi.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["noikhai"] = cbonguyenquan.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["MA_TINH"] = txtMaTinh.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["MA_BV"] = txtMaBV.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["macv"] = cbocongviec.Text;
                faNV3.Rows[faNV3.Selection.TopRow]["gioitinh"] = txtgioitinh.Text;
            }

            
        }
        private void Clear()
        {
            txtho.Text = "";
            txtten.Text = "";
            dtngaysinh.Value = null;
            txtgioitinh.Text = "";
            cbodantoc.SelectedValue = "01";
            cboquoctich.SelectedValue = "VN";
            txtCMND.Text = "";
            txtCMND1.Text = "";
            txtCMND2.Text = "";
            dtngaycap.Value = null;
            txtNoicap.Text = "";
            txtdiachi.Text = "";
            cbonguyenquan.SelectedValue = "";
            cbocongviec.SelectedValue = "";

        }
        private Boolean CheckBHXH(string sobhxh)
        {
            if (sobhxh.Length != 10 || sobhxh == "")
            {
                MessageBox.Show("nhËp l¹i sè bhxh");
                return false;
            }
            Int64 result = 0;
            if (Int64.TryParse(sobhxh, out result) == false)
            {
                MessageBox.Show("kh«ng nhËp ch÷ sè");
                return false;
            }
            string queryString = "select * from hosocn where  sobhxh = '" + sobhxh + "';";
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows == true)
                {

                    MessageBox.Show("Sè bhxh ®• tån t¹i");
                    return false;
                }
                return true;
            }
        }
        private Boolean CheckCapSo(string sobhxh)
        {
            if (Flaggrid != 1 && sobhxh.Substring(0, 2) == global::BHXH_NT.Properties.Settings.Default.MaTinh)
            {
                if (faNV3.Rows[faNV3.Selection.TopRow]["ho"].ToString().Trim() != txtho.Text.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["ten"].ToString().Trim() != txtten.Text.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["ngaysinh"].ToString().Substring(0, 10) != dtngaysinh.Value.ToString().Substring(0, 10) ||
                faNV3.Rows[faNV3.Selection.TopRow]["dantoc"].ToString().Trim() != cbodantoc.SelectedValue.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["quoctich"].ToString().Trim() != cboquoctich.SelectedValue.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["SOCMND"].ToString().Trim() != txtCMND.Text.ToString().Trim() + txtCMND1.Text.ToString().Trim() + txtCMND2.Text.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["NGAYCMND"].ToString().Substring(0, 10) != dtngaycap.Value.ToString().Substring(0, 10) ||
                faNV3.Rows[faNV3.Selection.TopRow]["noicap"].ToString().Trim() != txtNoicap.Text.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["diachi"].ToString().Trim() != txtdiachi.Text.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["noikhai"].ToString().Trim() != cbonguyenquan.Text.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["macv"].ToString().Trim() != cbocongviec.Text.ToString().Trim() ||
                faNV3.Rows[faNV3.Selection.TopRow]["gioitinh"].ToString().Trim() != txtgioitinh.Text.ToString().Trim())
                {
                    string queryString = "select * from INSO_DTL where  sobhxh = '" + sobhxh + "';";
                    using (SqlConnection connection =
                           new SqlConnection(connectionString1))
                    {
                        connection.Open();
                        SqlCommand command = connection.CreateCommand();
                        command.CommandText = queryString;
                        SqlDataReader reader = command.ExecuteReader();
                        if (reader.HasRows == true)
                        {

                            MessageBox.Show("Sè bhxh ®· cÊp tê b×a");
                            return false;
                        }

                    }
                }
            }
            return true;
        }
        #endregion

        #region "GetData"
        private void LoadNguyenQuanIntoForm(NguyenQuan nguyenquan)
        {

            cbonguyenquan.ClearItems();
            cbonguyenquan.DefColWidth = 100;

            nguyenquan.Names.Sort();
            foreach (string Item in nguyenquan.Names)
            {
                cbonguyenquan.AddItem(Item);
            }
            cbonguyenquan.AllowSort = true;
            cbonguyenquan.Columns[0].Caption = "Name";
            cbonguyenquan.ColumnWidth = 250;


        }
        private void GetTenDVI()
        {

            //string queryString = "SELECT *,RIGHT(RTRIM(SOBHXH),6) AS TAM, 0 As Ml, 0 As Mlyt, 0 As Hsl, 0 As Pc1, 0 As Pc2, 0 As Pc3, 0 As Pc4 "
            //+ "FROM HOSOCN WHERE Sobhxh<>''  AND Madvi='" + txtMaDVI.Text + "A"  + "' AND CHARINDEX('TA',SOBHXH) = 1 order by Sobhxh;";

            string queryString = "Select * from DMDONVI where MADVI = '" + txtMaDVI.Text + "A" + "'";



            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    txtTenDV.Text = reader["TENDVI"].ToString();
                }
                reader.Close();
            }
        }
        private void GetInformation()
        {

            //string queryString = "SELECT *,RIGHT(RTRIM(SOBHXH),6) AS TAM, 0 As Ml, 0 As Mlyt, 0 As Hsl, 0 As Pc1, 0 As Pc2, 0 As Pc3, 0 As Pc4 "
            //+ "FROM HOSOCN WHERE Sobhxh<>''  AND Madvi='" + txtMaDVI.Text + "A"  + "' AND CHARINDEX('TA',SOBHXH) = 1 order by Sobhxh;";

            string queryString = "exec sp_executesql N'SELECT RIGHT(RTRIM(a.SOBHXH),6) AS TAM,a.Sobhxh, a.Maqt, b.Ml, b.Mlyt, b.Hsl, b.Pc1, b.Pc2, b.Pc3, b.Pc4, b.Mdbhyt, b.Pa, "
            + " b.Congviec, c.Ho, c.Ten, c.Sokcb, c.Ngaysinh, c.Gioitinh, c.Dantoc, c.Quoctich, c.Socmnd, c.Ngaycmnd,  "
            + " c.Noicap, c.Noikhai, c.Noicapso, c.Mavung, c.Diachi, c.DiachiHK, c.Macv, c.Mapb, c.Madt, c.Ma_Tinh, c.Ma_bv, "
            + " c.Ngayduyet, c.Lancap, b.Madvi,d.TenDvi, c.Ngayps, c.Ma_Quan, c.Keysl  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh"
            + " left join DMDONVI d on d.Madvi = b.Madvi ,  "
            + " (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE Madvi=@P1  AND LEFT(Maqt,6)=Denthang AND "
            + " LEFT(Maqt,6)<=@P2  AND CHARINDEX(Pa,''TT/TU/TN/GN/DN/CD'')=0 GROUP BY Sobhxh, Loaidt, Ma_Nte) a  "
            + " WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
            + " AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt<>0 "
            + " AND b.Madvi=@P3 AND CHARINDEX(b.Pa,''TT/TU/TN/GN/DN/CD'')=0 AND CHARINDEX(''" + Settings.Default.MaTam + "'',a.SOBHXH) = 1 order by c.Ten',"
            + " N'@P1 varchar(7),@P2 varchar(6),@P3 varchar(7)', '" + txtMaDVI.Text + "A" + "', '" + ModuleBHXH.strMonths + "', '" + txtMaDVI.Text + "A" + "'";
            dsDSNV.dsNhanVien.Clear();
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                SqlDataAdapter myAdapter = new SqlDataAdapter();
                command.CommandText = queryString;
                command.CommandTimeout = 60;
                connection.Open();
                command.ExecuteNonQuery();
                myAdapter.SelectCommand = command;
                myAdapter.Fill(dsDSNV.dsNhanVien);
                int i = 1;
                faNV1.Rows.Count = 1;

                foreach (DataRow dtRow in dsDSNV.dsNhanVien)
                {
                    faNV1.Rows.Count = faNV1.Rows.Count + 1;
                    faNV1.Rows[i]["STT"] = i;
                    faNV1.Rows[i]["Sobhxh"] = dtRow["SOBHXH"];
                    if (dtRow["Sokcb"] != null && dtRow["Sokcb"].ToString() != "")
                    {
                        faNV1.Rows[i]["TAM"] = dtRow["Sokcb"].ToString().Substring(11);
                    }
                    faNV1.Rows[i]["SoKCB"] = dtRow["Sokcb"].ToString();
                    faNV1.Rows[i]["hoten"] = dtRow["Ho"].ToString().Trim() + " " + dtRow["Ten"].ToString().Trim();
                    faNV1.Rows[i]["ngaysinh"] = dtRow["ngaysinh"];
                    faNV1.Rows[i]["gioitinh"] = dtRow["gioitinh"];
                    faNV1.Rows[i]["dantoc"] = dtRow["dantoc"];
                    faNV1.Rows[i]["quoctich"] = dtRow["quoctich"];
                    faNV1.Rows[i]["socmnd"] = dtRow["socmnd"];
                    faNV1.Rows[i]["NGAYCMND"] = dtRow["NGAYCMND"];
                    faNV1.Rows[i]["noicap"] = dtRow["noicap"];
                    faNV1.Rows[i]["diachi"] = dtRow["diachi"];
                    faNV1.Rows[i]["noikhai"] = dtRow["noikhai"];
                    faNV1.Rows[i]["macv"] = dtRow["macv"];
                    faNV1.Rows[i]["ho"] = dtRow["ho"];
                    faNV1.Rows[i]["ten"] = dtRow["ten"];
                    faNV1.Rows[i]["MA_TINH"] = dtRow["MA_TINH"];
                    faNV1.Rows[i]["MA_BV"] = dtRow["MA_BV"];
                    faNV1.Rows[i]["KEYSL"] = dtRow["KEYSL"];
                    i++;

                }


            }
        }

        private void GetInformationGiam()
        {

            //string queryString = "SELECT *,RIGHT(RTRIM(SOBHXH),6) AS TAM, 0 As Ml, 0 As Mlyt, 0 As Hsl, 0 As Pc1, 0 As Pc2, 0 As Pc3, 0 As Pc4 "
            //+ "FROM HOSOCN WHERE Sobhxh<>''  AND Madvi='" + txtMaDVI.Text + "A"  + "' AND CHARINDEX('TA',SOBHXH) = 1 order by Sobhxh;";

            string queryString = "exec sp_executesql N'SELECT RIGHT(RTRIM(a.SOBHXH),6) AS TAM,a.Sobhxh, a.Maqt, b.Ml, b.Mlyt, b.Hsl, b.Pc1, b.Pc2, b.Pc3, b.Pc4, b.Mdbhyt, b.Pa, "
            + " b.Congviec, c.Ho, c.Ten, c.Sokcb, c.Ngaysinh, c.Gioitinh, c.Dantoc, c.Quoctich, c.Socmnd, c.Ngaycmnd,  "
            + " c.Noicap, c.Noikhai, c.Noicapso, c.Mavung, c.Diachi, c.DiachiHK, c.Macv, c.Mapb, c.Madt, c.Ma_Tinh, c.Ma_bv, "
            + " c.Ngayduyet, c.Lancap, b.Madvi,d.TenDVI, c.Ngayps, c.Ma_Quan, c.Keysl  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh"
            + " left join DMDONVI d on d.Madvi = b.Madvi , "
            + " (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE Madvi=@P1  AND LEFT(Maqt,6)=Denthang AND "
            + " LEFT(Maqt,6)<=@P2  AND CHARINDEX(Pa,''TT/TU/TN/GN/DN/CD'')=0 GROUP BY Sobhxh, Loaidt, Ma_Nte) a  "
            + " WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
            + " AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt=0 "
            + " AND b.Madvi=@P3 AND CHARINDEX(b.Pa,''TT/TU/TN/GN/DN/CD'')=0 AND CHARINDEX(''" + Settings.Default.MaTam + "'',a.SOBHXH) = 1 order by c.Ten',"
            + " N'@P1 varchar(7),@P2 varchar(6),@P3 varchar(7)', '" + txtMaDVI.Text + "A" + "', '" + ModuleBHXH.strMonths + "', '" + txtMaDVI.Text + "A" + "'";
            dsDSNV.dsNhanVien.Clear();
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                SqlDataAdapter myAdapter = new SqlDataAdapter();
                command.CommandText = queryString;
                command.CommandTimeout = 60;
                connection.Open();
                command.ExecuteNonQuery();
                myAdapter.SelectCommand = command;
                myAdapter.Fill(dsDSNV.dsNhanVien);
                int i = 1;
                faNV1.Rows.Count = 1;

                foreach (DataRow dtRow in dsDSNV.dsNhanVien)
                {
                    faNV1.Rows.Count = faNV1.Rows.Count + 1;
                    faNV1.Rows[i]["STT"] = i;
                    faNV1.Rows[i]["Sobhxh"] = dtRow["SOBHXH"];
                    if (dtRow["Sokcb"] != null && dtRow["Sokcb"].ToString() != "")
                    {
                        faNV1.Rows[i]["TAM"] = dtRow["Sokcb"].ToString().Substring(11);
                    }
                    faNV1.Rows[i]["Sokcb"] = dtRow["Sokcb"];
                    faNV1.Rows[i]["hoten"] = dtRow["Ho"].ToString().Trim() + " " + dtRow["Ten"].ToString().Trim();
                    faNV1.Rows[i]["ngaysinh"] = dtRow["ngaysinh"];
                    faNV1.Rows[i]["gioitinh"] = dtRow["gioitinh"];
                    faNV1.Rows[i]["dantoc"] = dtRow["dantoc"];
                    faNV1.Rows[i]["quoctich"] = dtRow["quoctich"];
                    faNV1.Rows[i]["socmnd"] = dtRow["socmnd"];
                    faNV1.Rows[i]["NGAYCMND"] = dtRow["NGAYCMND"];
                    faNV1.Rows[i]["noicap"] = dtRow["noicap"];
                    faNV1.Rows[i]["diachi"] = dtRow["diachi"];
                    faNV1.Rows[i]["noikhai"] = dtRow["noikhai"];
                    faNV1.Rows[i]["macv"] = dtRow["macv"];
                    faNV1.Rows[i]["ho"] = dtRow["ho"];
                    faNV1.Rows[i]["ten"] = dtRow["ten"];
                    faNV1.Rows[i]["MA_TINH"] = dtRow["MA_TINH"];
                    faNV1.Rows[i]["MA_BV"] = dtRow["MA_BV"];
                    faNV1.Rows[i]["KEYSL"] = dtRow["KEYSL"];
                    i++;

                }
            }
        }

        private void GetBHXHGIAM()
        {

            string queryString = "exec sp_executesql N'SELECT RIGHT(RTRIM(a.SOBHXH),6) AS TAM,a.Sobhxh, a.Maqt, b.Ml, b.Mlyt, b.Hsl, b.Pc1, b.Pc2, b.Pc3, b.Pc4, b.Mdbhyt, b.Pa, "
            + " b.Congviec, c.Ho, c.Ten, c.Sokcb, c.Ngaysinh, c.Gioitinh, c.Dantoc, c.Quoctich, c.Socmnd, c.Ngaycmnd,  "
            + " c.Noicap, c.Noikhai, c.Noicapso, c.Mavung, c.Diachi, c.DiachiHK, c.Macv, c.Mapb, c.Madt, c.Ma_Tinh, c.Ma_bv, "
            + " c.Ngayduyet, c.Lancap, b.Madvi, c.Ngayps, c.Ma_Quan, c.Keysl  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            + " (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE Madvi=@P1  AND LEFT(Maqt,6)=Denthang AND "
            + "  CHARINDEX(Pa,''TT/TU/TN/GN/DN/CD'')=0 GROUP BY Sobhxh, Loaidt, Ma_Nte) a  "
            + " WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
            + " AND b.Madvi=@P3  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt=0 AND CHARINDEX(b.Pa,''TT/TU/TN/GN/DN/CD'')=0 AND CHARINDEX(''" + Settings.Default.MaTam + "'',A.SOBHXH) = 0  order by C.Ten',"
            + " N'@P1 varchar(7),@P2 varchar(6),@P3 varchar(7)', '" + txtMaDVI.Text + "A" + "', '" + ModuleBHXH.strMonths + "', '" + txtMaDVI.Text + "A" + "'";

            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                faNV3.Rows.Count = 1;
                while (reader.Read())
                {
                    faNV3.Rows.Count = faNV3.Rows.Count + 1;
                    faNV3.Rows[i]["STT"] = i;
                    faNV3.Rows[i]["Sobhxh"] = reader["SOBHXH"];
                    if (reader["Sokcb"] != null && reader["Sokcb"].ToString() != "")
                    {
                        faNV3.Rows[i]["TAM"] = reader["Sokcb"].ToString().Substring(11);
                    }
                    faNV3.Rows[i]["Sokcb"] = reader["Sokcb"];
                    faNV3.Rows[i]["hoten"] = reader["Ho"].ToString().Trim() + " " + reader["Ten"].ToString().Trim();
                    faNV3.Rows[i]["ngaysinh"] = reader["ngaysinh"];
                    faNV3.Rows[i]["gioitinh"] = reader["gioitinh"];
                    faNV3.Rows[i]["dantoc"] = reader["dantoc"];
                    faNV3.Rows[i]["quoctich"] = reader["quoctich"];
                    faNV3.Rows[i]["socmnd"] = reader["socmnd"];
                    faNV3.Rows[i]["NGAYCMND"] = reader["NGAYCMND"];
                    faNV3.Rows[i]["noicap"] = reader["noicap"];
                    faNV3.Rows[i]["diachi"] = reader["diachi"];
                    faNV3.Rows[i]["noikhai"] = reader["noikhai"];
                    faNV3.Rows[i]["macv"] = reader["macv"];
                    faNV3.Rows[i]["ho"] = reader["ho"];
                    faNV3.Rows[i]["ten"] = reader["ten"];
                    faNV3.Rows[i]["MA_TINH"] = reader["MA_TINH"];
                    faNV3.Rows[i]["MA_BV"] = reader["MA_BV"];
                    faNV3.Rows[i]["KEYSL"] = reader["KEYSL"];
                    i++;


                }
                reader.Close();
            }
        }

        private void GetBHXH()
        {

            string strMonth= ModuleBHXH.strMonths;
            if (txtMaDVI.Text.Substring(0, 2) == "BI" || txtMaDVI.Text.Substring(0, 2) == "BD")
            {
                strMonth = Convert.ToInt32(Convert.ToInt32(ModuleBHXH.strMonths.Substring(0, 4)) + 1) + "01";
            }
            string queryString = "exec sp_executesql N'SELECT RIGHT(RTRIM(a.SOBHXH),6) AS TAM,a.Sobhxh, a.Maqt, b.Ml, b.Mlyt, b.Hsl, b.Pc1, b.Pc2, b.Pc3, b.Pc4, b.Mdbhyt, b.Pa, "
            + " b.Congviec, c.Ho, c.Ten, c.Sokcb, c.Ngaysinh, c.Gioitinh, c.Dantoc, c.Quoctich, c.Socmnd, c.Ngaycmnd,  "
            + " c.Noicap, c.Noikhai, c.Noicapso, c.Mavung, c.Diachi, c.DiachiHK, c.Macv, c.Mapb, c.Madt, c.Ma_Tinh, c.Ma_bv, "
            + " c.Ngayduyet, c.Lancap, b.Madvi, c.Ngayps, c.Ma_Quan, c.Keysl  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            + " (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE Madvi=@P1  AND LEFT(Maqt,6)=Denthang AND "
            + " LEFT(Maqt,6)<=@P2  AND CHARINDEX(Pa,''TT/TU/TN/GN/DN/CD'')=0 GROUP BY Sobhxh, Loaidt, Ma_Nte) a  "
            + " WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
            + " AND b.Madvi=@P3  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt<>0 AND CHARINDEX(b.Pa,''TT/TU/TN/GN/DN/CD'')=0 AND CHARINDEX(''" + Settings.Default.MaTam + "'',A.SOBHXH) = 0  order by C.Ten',"
            + " N'@P1 varchar(7),@P2 varchar(6),@P3 varchar(7)', '" + txtMaDVI.Text + "A" + "', '" + strMonth + "', '" + txtMaDVI.Text + "A" + "'";

            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                faNV3.Rows.Count = 1;
                while (reader.Read())
                {
                    faNV3.Rows.Count = faNV3.Rows.Count + 1;
                    faNV3.Rows[i]["STT"] = i;
                    faNV3.Rows[i]["Sobhxh"] = reader["SOBHXH"];
                    if (reader["Sokcb"] != null && reader["Sokcb"].ToString() != "")
                    {
                        faNV3.Rows[i]["TAM"] = reader["Sokcb"].ToString().Substring(11);
                    }
                    faNV3.Rows[i]["SoKCB"] = reader["Sokcb"].ToString();
                    faNV3.Rows[i]["hoten"] = reader["Ho"].ToString().Trim() + " " + reader["Ten"].ToString().Trim();
                    faNV3.Rows[i]["ngaysinh"] = reader["ngaysinh"];
                    faNV3.Rows[i]["gioitinh"] = reader["gioitinh"];
                    faNV3.Rows[i]["dantoc"] = reader["dantoc"];
                    faNV3.Rows[i]["quoctich"] = reader["quoctich"];
                    faNV3.Rows[i]["socmnd"] = reader["socmnd"];
                    faNV3.Rows[i]["NGAYCMND"] = reader["NGAYCMND"];
                    faNV3.Rows[i]["noicap"] = reader["noicap"];
                    faNV3.Rows[i]["diachi"] = reader["diachi"];
                    faNV3.Rows[i]["noikhai"] = reader["noikhai"];
                    faNV3.Rows[i]["macv"] = reader["macv"];
                    faNV3.Rows[i]["ho"] = reader["ho"];
                    faNV3.Rows[i]["ten"] = reader["ten"];
                    faNV3.Rows[i]["MA_TINH"] = reader["MA_TINH"];
                    faNV3.Rows[i]["MA_BV"] = reader["MA_BV"];
                    faNV3.Rows[i]["KEYSL"] = reader["KEYSL"];
                    i++;


                }
                reader.Close();
            }
        }
        private void GetNguyenQuan()
        {
            // Load the customer object from the existing XML file (if any)...
            if (File.Exists(XML_FILE_NGUYENQUAN) == true)
            {
                LoadObjectButton_Click(this, null);
            }
            else
            {
                MessageBox.Show(this.CreateHowToMsg(), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        private void GetCongViec()
        {
            // Load the customer object from the existing XML file (if any)...
            if (File.Exists(XML_FILE_CONGVIEC) == true)
            {
                LoadObjectCongviec();
            }
            else
            {
                MessageBox.Show(this.CreateHowToMsg(), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        private void LoadObjectCongviec()
        {
            // Load the customer object from the existing XML file (if any)...
            if (File.Exists(XML_FILE_CONGVIEC) == true)
            {
                // Load the customer object from the XML file using our custom class...
                CongViec congviec = ObjectXMLSerializer<CongViec>.Load(XML_FILE_CONGVIEC);

                if (congviec == null)
                {
                    MessageBox.Show("Unable to load customer object from file '" + XML_FILE_CONGVIEC + "'!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else  // Load customer properties into the form...
                {
                    this.LoadCongViecIntoForm(congviec);
                }
            }
            else
            {
                MessageBox.Show(this.CreateFileDoesNotExistMsg(), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        private void LoadCongViecIntoForm(CongViec congviec)
        {

            cbocongviec.ClearItems();
            cbocongviec.DefColWidth = 100;

            congviec.Names.Sort();
            foreach (string Item in congviec.Names)
            {
                cbocongviec.AddItem(Item);
            }
            cbocongviec.AllowSort = true;
            cbocongviec.Columns[0].Caption = "Name";
            cbocongviec.ColumnWidth = 250;
        }
        private CongViec CreateCongViec()
        {
            CongViec congviec = new CongViec();
            // Add Hobbies as a collection of strings...
            congviec.Names.Add(cbocongviec.Text);
            for (int i = 0; i < cbocongviec.ListCount; i++)
            {
                congviec.Names.Add(cbocongviec.Columns[0].CellValue(i));
            }

            return congviec;
        }
        private void GetDataforDanToc()
        {
            SqlConnection conn;
            SqlDataAdapter daDantoc;
            DataSet dsDantoc;

            conn = new SqlConnection(connectionString);
            // 1. instantiate a new DataSet
            dsDantoc = new DataSet();
            // 2. init SqlDataAdapter with select command and connection
            daDantoc = new SqlDataAdapter("select TEN_DTOC,DANTOC from DMDANTOC", conn);
            // 4. fill the dataset
            daDantoc.Fill(dsDantoc, "Dantoc");
            cbodantoc.DataSource = dsDantoc.Tables[0];
        }
        private void GetDataforLyDo()
        {
            SqlConnection conn;
            SqlDataAdapter daLydo;
            DataSet dsLydoc;

            conn = new SqlConnection(connectionString);
            // 1. instantiate a new DataSet
            dsLydoc = new DataSet();
            // 2. init SqlDataAdapter with select command and connection
            daLydo = new SqlDataAdapter("SELECT rtrim(TENCL) as TENCL,MACL FROM " + Settings.Default.DBName_Sothe + ".dbo.LYDOCL Lydocl", conn);
            // 4. fill the dataset
            daLydo.Fill(dsLydoc, "Lydo");
            cboLydo.DataSource = dsLydoc.Tables[0];

        }
        private void GetDataforQuoctich()
        {
            SqlConnection conn;
            SqlDataAdapter daQuoctich;
            DataSet dsQuoctich;

            conn = new SqlConnection(connectionString);
            // 1. instantiate a new DataSet
            dsQuoctich = new DataSet();
            // 2. init SqlDataAdapter with select command and connection
            daQuoctich = new SqlDataAdapter("select TEN_QTICH,QUOCTICH from DMQUOCTICH", conn);
            // 4. fill the dataset
            daQuoctich.Fill(dsQuoctich, "Quoctich");
            cboquoctich.DataSource = dsQuoctich.Tables[0];
        }
        private void GetDataforTinh()
        {
            SqlConnection conn;
            SqlDataAdapter daTinh;
            DataSet dsTinh;

            conn = new SqlConnection(connectionString);
            // 1. instantiate a new DataSet
            dsTinh = new DataSet();
            // 2. init SqlDataAdapter with select command and connection
            daTinh = new SqlDataAdapter("select TEN_TINH,MA_TINH from DMTINH", conn);
            // 4. fill the dataset
            daTinh.Fill(dsTinh, "Tinh");
            cboNoiCap.DataSource = dsTinh.Tables[0];
        }
        private NguyenQuan CreateNguyenQuan()
        {
            NguyenQuan nguyenquan = new NguyenQuan();
            // Add Hobbies as a collection of strings...
            nguyenquan.Names.Add(cbonguyenquan.Text);
            for (int i = 0; i < cbonguyenquan.ListCount; i++)
            {

                nguyenquan.Names.Add(cbonguyenquan.Columns[0].CellValue(i));

            }

            return nguyenquan;
        }
        #endregion

        private void btnSotam_Click(object sender, EventArgs e)
        {
            string queryString;
            string strMonth = ModuleBHXH.strMonths;
            if (txtMaDVI.Text.Substring(0, 2) == "BI")
            {
                strMonth = Convert.ToInt32(Convert.ToInt32(ModuleBHXH.strMonths.Substring(0, 4)) + 1) + "01";
            }
           
            queryString = "exec sp_executesql N'SELECT max(b.Sobhxh) as sobhxh"
            + " FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            + " (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE Madvi=@P3  "
            + " AND LEFT(Maqt,6)=Denthang AND LEFT(Maqt,6)<=@P4  AND CHARINDEX(Pa,''TT/TU/TN/GN/DN/CD'')=0 "
            + " GROUP BY Sobhxh, Loaidt, Ma_Nte) a  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            + " AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang AND b.Madvi=@P5  "
            + " AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt<>0 AND CHARINDEX(b.Pa,''TT/TU/TN/GN/DN/CD'')=0 ', "
            + " N'@P1 varchar(7),@P2 varchar(6),@P3 varchar(7),@P4 varchar(6),@P5 varchar(7)', "
            + " '" + txtMaDVI.Text + "A', '" + strMonth + "', '" + txtMaDVI.Text + "A', '" + strMonth + "', '" + txtMaDVI.Text + "A'";

            using (SqlConnection connection =
                  new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    txtSotam.Text = "Y" + Convert.ToInt32(Convert.ToInt32(reader["SOBHXH"].ToString().Substring(1)) + 1).ToString();
                }
                reader.Close();
            }
        }

        private void btnTS_Click(object sender, EventArgs e)
        {
             QuatrinhBHXH dsQuatrinh = new QuatrinhBHXH();
            DataRow newRow = null;
            
            string sobhxh = "", Maxqt = "";

            if (Flaggrid == 1)
            {
                sobhxh = faNV1.Rows[faNV1.Selection.TopRow]["Sobhxh"].ToString();
            }
            else if (Flaggrid == 3)
            {
                sobhxh = faNV3.Rows[faNV3.Selection.TopRow]["Sobhxh"].ToString();
            }

            string queryString = "exec sp_executesql N'SELECT TOP 1 Maqt, Madvi FROM HSC47_DTL WHERE Sobhxh=@P1  ORDER BY Maqt DESC', N'@P1 varchar(10)','" + sobhxh + "'";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Maxqt = reader["Maqt"].ToString().Substring(0, 6);
                }
            }



            queryString = "exec sp_executesql N'EXEC QUATRINHCN @P1 , @P2 , @P3 ', "
                + " N'@P1 varchar(16),@P2 varchar(6),@P3 varchar(6)', '" + sobhxh + "', '190101', '" + Maxqt + "'";
            faQuaTrinh.Rows.Count = 1;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    newRow = dsQuatrinh.Tables[0].NewRow();
                    if (reader["loaips"].ToString() == "1" && reader["pa"].ToString() != "SB")
                    {

                        newRow["Madvi"] = reader["Madvi"];
                        newRow["Tuthang"] = reader["Maqt"].ToString().Substring(0, 6);
                        newRow["Loaidt"] = reader["Loaidt"];
                        newRow["Ma_nte"] = reader["Ma_nte"];
                        newRow["Mucluong"] = reader["ML"];
                        newRow["Hsl"] = reader["Hsl"];
                        newRow["PCCV"] = reader["pc1"];
                        newRow["PCTN"] = reader["pc2"];
                        newRow["PCNG"] = reader["pc3"];
                        newRow["PCKV"] = reader["pc4"];
                        newRow["pa"] = reader["pa"];
                        newRow["loaips"] = reader["loaips"];

                        if (reader["Congviec"].ToString().Trim() == "")
                        {
                            newRow["Congviec"] = cbocongviec.Text;
                        }
                        else
                        {
                            newRow["Congviec"] = reader["Congviec"];
                        }
                    }
                }
            }
        }

      

        
    }
}
